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!

 

 

 

 

Previous Purchases Analysis
Next Week Based Time Intelligence for the Custom Calendars in Power BI

No Comment

Leave a Reply