POWER BI: Modeling

Time Intelligence Functions

DATEADD function

DATEADD is a flexible and powerful DAX function. DATEADD takes in a date columnm, the offset, and the time interval for the offest. The offset is the number of intervals to go forward or back in time.

Example Formulas:

                   Rev Previous_Month = CALCULATE(SUM(TransactionData[Revenue]),DATEADD(CalenderDate[Date],-1,MONTH))
	            

                Rev Previous_Quarter = CALCULATE(SUM(TransactionData[Revenue]),DATEADD(CalenderDate[Date],-1,QUARTER))
	            

                   Rev Previous_Year = CALCULATE(SUM(TransactionData[Revenue]),DATEADD(CalenderDate[Date],-1,YEAR))
	            

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:

This is useful to compare how sales between differ between different months or quarters. You can add in an extra column to compare the relative percentage differences.