POWER BI: Modeling

Time Intelligence Functions

STARTOF functions

Returns the first date of the month, quarter or year.

Example Formulas:

Use with CALCULATE to return revenue on the first day of the month, quarter or year.

                   StartofMonth_revenue = CALCULATE(SUM(TransactionData[Revenue]),STARTOFMONTH(CalenderDate[Date]))
	            

                   StartofQuarter_revenue = CALCULATE(SUM(TransactionData[Revenue]),STARTOFQUARTER(CalenderDate[Date]))
	            

                   StartofYear_revenue = CALCULATE(SUM(TransactionData[Revenue]),STARTOFYEAR(CalenderDate[Date]))
	            

Power BI Demo:

How to Make:

• In Power BI Desktop create a new measure using the formulas above.
• Under visualizations select table
• Select or Add in the date column (i.e CalenderDate[Date]) from the calendar date table.
• Select or Add in the new measures to the values section

Related Functions and Formulas:

• Opening Balance