LOADING

Type to search

Using Time Intelligence – MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data

To Know more about the Different Corporate Training & Consulting Visit our website www.Instrovate.com Or Email : info@instrovate.com or WhatsApp / Call at +91 74289 52788

Data Analytics PowerBI

Using Time Intelligence – MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data

Share

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):

 

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 29

Create Relationship between both tables:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 30

Lets start exploring Time Intelligence functions

MTD:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 31

Analyzise Profit month wise with respect to Start of mont for every year:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 32

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 33

Add QTD

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 34

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 35

Same add Year To Date  and do little formatting using format option:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 36

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:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 37

Now lets create a Map using Geographical hierarchy for country State and city:

 

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 38

Create a Line chart for Profit with respect to Sub categories:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 39

Now we have created different visualizations, lets create a report on combining all visualizations on single page:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 40

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)

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 41

 

Select wallpaper to set in background for report:

Using Time Intelligence - MTD , QTD , YTD Function in DAX in Microsoft Power BI to Find Valuable Knowledge & Actionable Insights From your Data 42

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.

Remember to Susbscribe the YouTube Video Above , If you don’t want to miss any new free learning.

Tags:

You Might also Like

Leave a Comment

Your email address will not be published. Required fields are marked *