POWER BI: Modeling

Time Intelligence Functions

Closing Balance functions

Useful for an account balance. Will get the last record for the given month, quarter or year depending on the function used.

At least two paramters: An expression and a date column.

Example Formulas:

                  
                  closingBalanceMonth = CLOSINGBALANCEMONTH(SUM(TransactionData[Revenue]),CalenderDate[Date])
                   
	            

                  
                  closingBalanceQuarter = CLOSINGBALANCEQUARTER(SUM(TransactionData[Revenue]),CalenderDate[Date])
                   
	            

                  
                  closingBalanceYear = CLOSINGBALANCEYEAR(SUM(TransactionData[Revenue]),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:

Note: If the last date, the closing balance date, has no values for the expression used, it will return blank. For example closing balance month is empty in February since there was no revenue data for Feb 29th 2016 in this sample data.