Forum Discussion
Two date slicers for one visual
- 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 - _ForecastHere 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 ๐
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 ๐