Sunday, July 18, 2021

How to show Previous 6 Months of data based on the Selected Date from a Slicer

How to show Last 6 Months of data based on the Selected Month from a Slicer
Scenario :
Lets suppose we wants to show the last 6 Months data based on single Date/Month selected from a Slicer. We can achieve this scenario using various techniques. However, the following is the one of the method, based on reference Date table as explained below.

The Dat Model is as follows :

Introduction to the tables that we we are using in our Scenario :
tbl_Calendar is the Calendar table having all the Date dimensions like Date, Month, Year ..etc.

Cal_Previous_Dates is the subset of the tbl_Calendar table derived using the below DAX. You can generate a subset of table from another table using different ways.
Please note that, the table "Cal_Previous_Dates" is playing a vital role in implementing our Scenario.

Cal_Previous_Dates = SUMMARIZE(tbl_Calendar, tbl_Calendar[cDate], tbl_Calendar[Month_Year], tbl_Calendar[SortKey_Month] )



here, SortKey_Month is a calculated column in tbl_Calendar using the below DAX logic which helps to sort the Months so that we can display them in right order on the Visuals.
SortKey_Month = tbl_Calendar[Year]+tbl_Calendar[Month_No]+INT(RIGHT(tbl_Calendar[Year],2))*12

Next we need to understand the relationship between Cal_Previous_Dates and  tbl_Calendar is inactive, which will be activated whenever required during the calculations.

tbl_Sales is the fact table with Sales figures like Gross_Sales, Units_Sold..etc, from which we we will calculate Net Sales.

Net Sales = SUM(tbl_Sales[Gross_Sales])-[Total Cost]
Total Cost = SUM(tbl_Cost[COGS])

Note:
From the Model if we Observe, the tbl_Sales is not directly filtering from the tbl_Calendar. It is filtering via tbl_OrderDetails

DAX Logic to Calculate the last / Previous 6 Months Net Sales based on the single selection from Month Slicer:

Sales Prev_6M = 
VAR Ref_Date=MAX('tbl_Calendar'[cDate])
VAR NumOfMonths=6
VAR Prev_6M_Dates=
  DATESINPERIOD ('Cal_Previous_Dates'[cDate], Ref_Date,-NumOfMonths, MONTH)
VAR Result=
CALCULATE ( [Net Sales] , 
--SELECTEDMEASURE(),
    REMOVEFILTERS('tbl_Calendar'),
                        KEEPFILTERS(Prev_6M_Dates),
USERELATIONSHIP('tbl_Calendar'[cDate],  'Cal_Previous_Dates'[cDate])
                        )
RETURN  
Result

Now the Result of the measure "Sales Prev_6M" is as follows :

here, "Month_Year" in the Slicer is taken from main Calendar table "tbl_Calendar" , but the Month_Year in the Table and Charts has to take from the table "Cal_Previous_Dates".

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog