POWER BI: Modeling

Time Intelligence Functions

DATESBETWEEN function

Use with CALCULATE to evaluate an expression between a fixed or dynamic date range.

Example Formulas:

                   Static_Dates_Between = CALCULATE(SUM(TransactionData[Revenue]),
                   DATESBETWEEN(CalenderDate[Date],DATE(2016,01,01),DATE(2017,12,31)))
	            

                   Dynamic_Last_Dates = CALCULATE(SUM(TransactionData[Revenue]),
                   DATESBETWEEN(CalenderDate[Date],DATE(2016,01,01),LASTDATE(CalenderDate[Date])))
	            

                   All_Revenue = CALCULATE(SUM(TransactionData[Revenue]),ALL(CalenderDate))
	            

Warning: Power BI could crash if you use two dynamic date ranges in a table with the DATESBETWEEN function.

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:

Using fixed dates as shown in the Static_Dates_Between formula will give you the same value for all the rows. (it is the same value in any context). However, Dynamic_Last_Dates changes with respect to the rows in the table. Essentially a Running Total is created.

To return all revenue dynamically, use the ALL function around the calendar table as shown in the All_Revenue formula.