POWER BI: Modeling

Time Intelligence Functions

ENDOF functions

Returns the last day of the month, quarter or year.

Example Formulas:

                   EndOf_Month = CALCULATE(SUM(TransactionData[Revenue]),ENDOFMONTH(CalenderDate[Date]))
	            

                   EndOf_Quarter = CALCULATE(SUM(TransactionData[Revenue]),ENDOFQUARTER(CalenderDate[Date]))
	            

                   EndOf_Year = CALCULATE(SUM(TransactionData[Revenue]),ENDOFYEAR(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

Additional Comments:

Useful for an account balance. Returns the last day of revenue in the current context.

If there is no data for a particular date, it will return blank instead of the last value. For instance, the second row in EndOfMonth is blank because there is no data for Feb 29th 2016 in this data set.

Related Functions and formulas: