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 Sale...
  • Sergii24's avatar
    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 ๐Ÿ™‚