POWER BI: Modeling

Time Intelligence Functions

DATESMTD, DATESQTD, DATESYTD function

Returns a single column table of dates for month, quarter or year to date.

Example Formulas:

Use with CALCULATE to evaluate month-to-date, quarter-to-date and year-to-date revenue

                   Revenue_Month_to_Date = CALCULATE(SUM(TransactionData[Revenue]),DATESMTD(CalenderDate[Date]))
	            

                   Revenue_Quarter_to_Date = CALCULATE(SUM(TransactionData[Revenue]),DATESQTD(CalenderDate[Date]))
	            

                   Revenue_Year_to_Date = CALCULATE(SUM(TransactionData[Revenue]),DATESYTD(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

Related Functions and formulas:

• TOTALMTD
• TOTALQTD
• TOTALYTD