POWER BI: Modeling

Time Intelligence Functions

OPENINGBALANCE functions

Returns the last date for the given month, quarter or year.

Formulas:

                   openingBalanceMonth = OPENINGBALANCEMONTH(SUM(TransactionData[Revenue]),CalenderDate[Date])
	            

                   openingBalanceQuarter = OPENINGBALANCEQUARTER(SUM(TransactionData[Revenue]),CalenderDate[Date])
	            

                   openingBalanceYear = OPENINGBALANCEYEAR(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:

Opening balance month for February displays the last day of revenue in January.