Sunday, March 10, 2019

How to Filter data dynamically using Time Intelligence Selector

How to dynamically view YTD, QTD and MTD data using Time Intelligence Selector 
Scenario :
Suppose we have the data Model in our Power BI Report as follows..

We have the "SelectMeasure" table with data as follows..

Next in this Table, we will create a new Measure using the below DAX Expression :
SelectorNum = MIN(SelectMeasure[SelectorNumber])

Next we create a Slicer (Horizontal Orientation) on the report using the above table. This will be used as a dynamic Query Filter for viewing the data.
Next we create the required Measures using the below DAX Expressions :
SumSales = SUM(FactSales[Net_Sales])
ActualMTD = CALCULATE([SumSales], DATESMTD(FactSales[Order_Date]))
ActualQTD
= CALCULATE([SumSales], DATESQTD(FactSales[Order_Date]))
ActualYTD
= CALCULATE([SumSales], DATESYTD(FactSales[Order_Date]))

Notes :
     The "[SumSales] is the Total Sum of Sales.
     The other Actual measures will be created based on the measure [SumSales].
     [ActualMTD] - returns the SumSales of the Current Month(eg : FY16Q4M3).
     [ActualQTD] - returns the SumSales of the Current Quarter(eg: FY16Q4).
     [ActualYTD] - returns the SumSales of the Current Year(eg: FY16)


Next we will create a new Measure [ActualSelect], using the Switch Function which will be selecting the values from the above Actual Measures based on our Selector (i.e., Slicer)
ActualSelect = SWITCH([SelectorNum]
  , 1, [ActualMTD]
  , 2, [ActualQTD]
  , 3, [ActualYTD]
)

Now we can use the measure [ActualSelect], in the report, to dynamically Filter the Data in the the Report based on the Slicer/Selector items as follows..

Notes :
In the above Card in blue color, the label ( FY16 Sales : 219 M) is customized using the below DAX Expression :
SumLabel = "FY"&RIGHT(MAXX(FactSales,YEAR(FactSales[Order_Date])),2)&" Sales : "& FORMAT([SumSales]/100000,"0 M")

Suppose if we have a Scenario (not related to above Article) where we have the data with Actuals and Forecast, in that case, we can calculate the Actual/Forecast using the below similar DAX Functions :
Actual = CALCULATE([SumAmount], Scenario[ScenarioName] = "Actual")
Forecast = CALCULATE([SumAmount], Scenario[ScenarioName] = "Forecast")
Actual/Forecast = SUMX(VALUES(FactSales[FiscalYear]),IF(ISBLANK([Forecast]), [Actual], [Forecast]))

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