POWER BI: Modeling

Time Intelligence Functions

DATESINPERIOD function

Returns a single column table of dates specified by a start date, an offset, and the time interval for the offset.

Example Formulas:

Use with CALCULATE to return revenue from the previous 7 Days.

                   Revenue Last Full Week = CALCULATE(SUM(TransactionData[Revenue]),
                   DATESINPERIOD(CalenderDate[Date],FIRSTDATE(CalenderDate[Date])-7,7,DAY))
	            

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:

Revenue Last Full Week returns the last 7 days of revenue. For example on Jan 8th, it returns the sum of revenue from Jan 1st to Jan 7th. On Jan 9th, it returns the sum of revenue from Jan 2nd to January 8th and so on.

It does not return anything on January 1st 2016, since there is no data from Dec 25th 2015 to Dec 31st 2015.

Related Functions and formulas:

• Last 30 Days Revenue