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