Time Intelligence in Power BI

Picture of alphabold

alphabold

Introduction

Time Intelligence is one of the most important aspects of Power BI reporting. Good reporting practice utilizes minimalistic DAX expressions and avoids complexities to build the reports and analytics. Since Power BI is an advanced tool that caters to almost any kind of reporting need, it offers various time intelligence functions, some of which we will explore in this blog.  Time Intelligence plays a significant role in financial planning, budgeting, accounting, or any area which has to do with analytics based on time. Let’s get started.

Demonstration

I have a demo Power BI model containing a Date Table (Calendar) which has an active relationship with the Sales table. 

Infographics show the Power BI model containing a Date Table
I have plotted a measure for Sales Quantity:
Infographics show the Sales Quantity
Let’s suppose we want to compare the current Sales Quantity with different timeframes from the Previous Year. Here is how we can do that: SAMEPERIODLASTYEAR(): Depending on the current date selection, this function automatically evaluates the DAX expression for the previous year’s date criteria. For example, in the screenshot below, you can see that Sales Quantity LY plots the values for the previous year’s sales quantity i.e., 2011.

Read more about The Ultimate Salesforce Implementation Checklist for CIOs

Infographics show the Sales Quantity LY

Is Your Business Utilizing Power BI to Its Full Potential?

Partner with AlphaBOLD to maximize the value of your data through advanced Power BI solutions. Let's discuss how we can help.

Request a Demo
DATEADD(): We can also use the DATEADD() function, which provides more flexibility in timeline selection. Here are a few examples:
Infographics show the DATEADD() function
As an alternative to SAMEPERIODLASTYEAR(), I have used DATEADD() in the screenshot above. The results are the same. Similarly, if we want to check Sales Quantity for Previous Month, Quarter, or Day, we can do that as well just by tweaking the DATEADD() function quite easily. See the screenshot below for the Previous Month calculation.
Infographics show the Sales Quantity for Previous Month
Ready to connect with our Power BI consultants? Contact us! DATESMTD(), DATESYTD(), DATESQTD(): If you want to calculate any running totals for your metric(s) by Month-to-Date (MTD), Quarter-to-Date (QTD), or Year-to-Date (YTD), these functions are of tremendous help out of the box. Month-to-Date (MTD) Calculation for Sales Quantity: (Screenshot below)
Infographics show the Month-to-Date (MTD) Calculation for Sales Quantity

What if you are trying to compute Sales Quantity for the Previous Year’s MTD/YTD/QTD?

There is a neat trick to it. See screenshot below for Previous Year’s MTD calculation.
Infographics show the Previous Year’s MTD calculation

If we just substitute the Sales Quantity measure with Sales Quantity LY, keeping our DATESMTD function applied, it does exactly that, cool stuff.

What if you need to calculate YTD Sales according to Fiscal Year and not by Calendar Year?

For that, we need to explicitly provide the function DATESYTD() with a parameter for the Fiscal Year’s ending date.
Infographics show the function DATESYTD() with a parameter for the Fiscal Year’s ending date
The value “06-30” show that our fiscal year ends on this provided date, & the YTD running total calculation is being computed starting from 1st July to 30th June.

Are Your Data Strategies Aligned with Business Goals?

Ready to optimize your business growth? Learn how AlphaBOLD Power BI's services can help you with the foresight needed to make smarter, data-driven decisions.

Request a Demo

Conclusion

So, there you have it! You can do a lot more with these functions as they are the perfect solution for time intelligence problems. And most importantly, these calculations are dynamic, meaning if I place any filter, e.g., Location, Product Type, these calculations will reflect the data for those selections (provided the data model has the existing relationships). Feel free to reach out to us if you have any questions by using the comment box below! 

Explore Recent Blog Posts

Infographics show the 2021 MSUS Partner Award winner

Related Posts