DAX

Top 10 Ready to Use Time Intelligence Calculations for Power BI

Sometimes, It is required to Copy & reuse the ready made calculations in your Power BI Model without understanding the logic behind the scene. Even, Power BI Desktop June 2017 Release now features ready to use measures that can be applied to your data model.

Having a compiled list of these measures gives you the jump start on developing complex reports & enrich your model within matter of minutes and that’s what the purpose of self service BI is.

In this article, I have compiled the list of Top 10 time intelligence calculations for you to use in your data model.

Note: It is recommended to use the separate Date/Calendar Table in the data model for the correct functioning of the time intelligence calculations. Those who do not know how to create a date table, please visit my blog post here.

(1) To calculate the TotalSales, TotalCost & TotalQuantity Measures

TotalSales:= SUM(FactSales[SalesAmount])
TotalCost := SUM(FactSales[Cost])
TotalQuantity:= SUM(FactSales[SalesQuantity])

(2) To calculate Prior Year Sales

PriorYearSales:= CALCULATE([TotalSales],SAMEPERIODLASTYEAR(Date[Date]))
                     OR
PriorYearSales:= CALCULATE([TotalSales],DATEADD(Date[Date],-1,YEAR))

You can also use DATEADD function. (Check it out here). This function will allow you to specify custom last parameter(QUARTER, MONTH,DAY).

(3)Year To Date( YTD), Quarter To Date (QTD) & Month To Date(MTD)

YTD:= CALCULATE([TotalSales],DATESYTD(Date[Date]))
QTD:= CALCULATE([TotalSales],DATESQTD(Date[Date]))
MTD:= CALCULATE([TotalSales],DATESMTD(Date[Date]))

(4) Previous Year YTD ( PY YTD), Previous Year QTD (PY QTD) & Previous Year(PY MTD)

 PY YTD := CALCULATE([YTD],SAMEPERIODLASTYEAR(Date[Date]))
 PY QTD := CALCULATE([QTD],SAMEPERIODLASTYEAR(Date[Date]))
 PY MTD := CALCULATE([MTD],SAMEPERIODLASTYEAR(Date[Date]))

(5) Previous QTD, Previous MTD Sales

PreviousMTD = CALCULATE([TotalSales],
                 DATESBETWEEN(Date[Date],
                  FIRSTDATE(PREVIOUSMONTH(Date[Date])),
                    LASTDATE(DATEADD(Date[Date],-1,MONTH))))

PreviousQTD = CALCULATE([TotalSales],
                 DATESBETWEEN(Date[Date],
                  FIRSTDATE(PREVIOUSMONTH(Date[Date])),
                    LASTDATE(DATEADD(Date[Date],-1,QUARTER))))

(6) Prior Year Total Sales ( At the GRAND TOTAL Level)

 PY TotalSales:= CALCULATE([TotalSales],
                        PARALLELPERIOD(Date[Date],-1,YEAR))

(7) YoY Calculations

 YoY Growth := [TotalSales] - [PriorYearSales]
 YoY Growth % := DIVIDE([YoY Growth], [PriorYearSales])

(8) YoY YTD Growth Calculations

   YoY YTD Growth := [YTD] - [PY YTD]
   YoY YTD Growth % := DIVIDE([YoY YTD Growth],[PY YTD])

(9)  KPI to check whether the current YTD Sales reached the value of the total sales of the prior year

 KPI := DIVIDE( [YTD],[PY TotalSales])

YTD – Checkout point 2
PY TotalSales – Checkout point 6

(10) Moving Annual Total, Rolling 90 day TotalSales

Moving Annual Total:= CALCULATE([TotalSales],
                         DATESINPERIOD(Date[Date],
                             LASTDATE(Date[Date],-1,YEAR))
Rolling 90 Day TotalSales:= CALCULATE([TotalSales],
                                DATESBETWEEN(Date[Date],
                                    FIRSTDATE(DATEADD(
                                       LASTDATE(Date[Date]),-89,DAY)),
                            LASTDATE(Date[Date])))

FeedBack?

I would love to hear your feedback on these calculations! Please feel free to contact us if you have any questions.

Thanks for reading!

 

 

 

 

Categories: DAX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s