Using Time Intelligence – MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data
Remember to Susbscribe the YouTube Video Above , If you don’t want to miss any new free learning.
Use the Power of DAX in your Microsoft Power BI Dashboard . Here in this blog we use Time Intelligence Function to calculate YTD, MTD , QTD in a Denormalized Data set like sample super store ? But in the Real Big Data World ? Power BI has the edge to create Relationships between Different Fact/ Data and DIM / Lookup table and use the same Data Model to create Multiple Dashboard . Here in this blog you can learn How to use DAX Functions in Microsoft Power BI to Calculate MTD , QTD , YTD ?
Contact us if you are looking for a Corporate Training on Power BI and take the advantage of the excellent features the tool offers for FREE for your Big Data Analytics and Digital Marketing Analytics requirement.
We can create exciting and more meaningful reports using DAX in Power BI.
Time Intelligence functions (using DAX) are very important in Power BI to analyse Revenue /Profit etc for different organizations.
Today we are going to create a report to analyse Profit Month to Date (MTD), Quarter To Date (QTD) & Year To Date (YTD). We will notice how Category and Geometrical regions affects our calculations (w.r.t. MTD, QTD, YTD).
First, lets upload our Sample Superstore data set (in excel format).
Our Order file is uploaded in Power BI Desktop.
Now create a running Calendar using DAX (I have explained it in my training how to create Running Calendar):
Create Relationship between both tables:
Lets start exploring Time Intelligence functions
Analyzise Profit month wise with respect to Start of mont for every year:
Same add Year To Date and do little formatting using format option:
Using conditional formatting we can add bars and using bars for formatting we can easily identify ups and downs in profit for all functions i.e. MTD, QTD and YTD.
Create a custom visual (Bullet chart) to analyse profit and target profit with respect to category:
Now lets create a Map using Geographical hierarchy for country State and city:
Create a Line chart for Profit with respect to Sub categories:
Now we have created different visualizations, lets create a report on combining all visualizations on single page:
Here we are analysing Month To Date (MTD), Quarter To Date (QTD) and Year To Date (YTD) Profit according to category, sub category, and geographical region.
Explore Interactions of different visuals for Category ( Technology)
Select wallpaper to set in background for report:
We have created a meaningful report using DAX, I will explain more DAX functions in my training sessions such as how to calculate cumulative Totals, Running total, Date Difference, and how to use Logical Functions , Statistical functions, Filter functions etc.