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

——————————————————————————————————–

Source link