Forum Discussion

Marcoss's avatar
Marcoss
Frequent Visitor
3 years ago
Solved

Two date slicers for one visual

Hello everyone,

I would like to create the visual as below using two slicers, one slicer for filtering Sales data, and second slicer for filtering Forecast data.
For example: I want to analyze Sales data from the month 2023-07 (selection from the first slicer) and compare it to Forecast data from the month 2023-05 (selection from the second slicer). The "Difference" measure should count the difference between Sales and Forecast.

Below there are sample input data.

Do you have any ideas?

 

YearMonthYear-MonthCountrySalesForecastDifference
202212022-01UK7039472224-1830
202222022-02UK6728967356-67
202232022-03UK3281933410-591
202242022-04UK85185816073578
202252022-05UK1998521184-1199
202262022-06UK29108270702038
202272022-07UK5434556791-2446
202282022-08UK8826888533-265
202292022-09UK84577696761
2022102022-10UK2574326593-850
2022112022-11UK8224888828-6580
2022122022-12UK68567206-350
202312023-01UK95430940941336
202322023-02UK40023364613562
202332023-03UK6114661329-183
202342023-04UK99505957243781
202352023-05UK9109594557-3462
202362023-06UK95013101474-6461
202372023-07UK43351394063945
202212022-01US5967364566-4893
202222022-02US27674254602214
202232022-03US1574917214-1465
202242022-04US81957801541803
202252022-05US4895148266685
202262022-06US37939344493490
202272022-07US3828541042-2757
202282022-08US8192684056-2130
202292022-09US8850890278-1770
2022102022-10US2682925890939
2022112022-11US7988580604-719
2022122022-12US7398279087-5105
202312023-01US1926318627636
202322023-02US3040930470-61
202332023-03US2983431027-1193
202342023-04US5455355590-1037
202352023-05US101189986132
202362023-06US2179920862937
202372023-07US46333447581575
Need Help
  • Sergii24's avatar
    Sergii24
    3 years ago

    Hi Marcoss, to achieve such result you need to work on data model and create a disconnected table of dates (in your case Year-Month). Your final result should be similar to this:

     

    You can create such table using the following expression:

     

    Disconnected Calendar for Fcst = DISTINCT( 'Fact Table'[Year-Month] )

     

     

    Now, you can build a mesure, which will use a relationship when calculating Sales and the disconnected filter to calculate Fcst:

     

    Difference = 
VAR _DisconnectedMonth = SELECTEDVALUE( 'Disconnected Calendar for Fcst'[Year-Month] ) 
VAR _Sales = SUM( 'Fact Table'[Sales] )
VAR _Forecast = 
    CALCULATE(
        SUM( 'Fact Table'[Forecast] ),
        'Calendar Table'[Year-Month] = _DisconnectedMonth
    )
RETURN _Sales - _Forecast

     

     

    Here is the final result for comparison of all sales vs fcst of 2022-01:

    Remember that diconnected filter should allow only single selection.

    You can find a sample *.pbix file here to play around and better understand how it works 🙂

     

1 Reply

  • Sergii24's avatar
    Sergii24
    Icon for Super User rankSuper User
    3 years ago

    Hi Marcoss, to achieve such result you need to work on data model and create a disconnected table of dates (in your case Year-Month). Your final result should be similar to this:

     

    You can create such table using the following expression:

     

    Disconnected Calendar for Fcst = DISTINCT( 'Fact Table'[Year-Month] )

     

     

    Now, you can build a mesure, which will use a relationship when calculating Sales and the disconnected filter to calculate Fcst:

     

    Difference = 
VAR _DisconnectedMonth = SELECTEDVALUE( 'Disconnected Calendar for Fcst'[Year-Month] ) 
VAR _Sales = SUM( 'Fact Table'[Sales] )
VAR _Forecast = 
    CALCULATE(
        SUM( 'Fact Table'[Forecast] ),
        'Calendar Table'[Year-Month] = _DisconnectedMonth
    )
RETURN _Sales - _Forecast

     

     

    Here is the final result for comparison of all sales vs fcst of 2022-01:

    Remember that diconnected filter should allow only single selection.

    You can find a sample *.pbix file here to play around and better understand how it works 🙂