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?
| Year | Month | Year-Month | Country | Sales | Forecast | Difference |
| 2022 | 1 | 2022-01 | UK | 70394 | 72224 | -1830 |
| 2022 | 2 | 2022-02 | UK | 67289 | 67356 | -67 |
| 2022 | 3 | 2022-03 | UK | 32819 | 33410 | -591 |
| 2022 | 4 | 2022-04 | UK | 85185 | 81607 | 3578 |
| 2022 | 5 | 2022-05 | UK | 19985 | 21184 | -1199 |
| 2022 | 6 | 2022-06 | UK | 29108 | 27070 | 2038 |
| 2022 | 7 | 2022-07 | UK | 54345 | 56791 | -2446 |
| 2022 | 8 | 2022-08 | UK | 88268 | 88533 | -265 |
| 2022 | 9 | 2022-09 | UK | 8457 | 7696 | 761 |
| 2022 | 10 | 2022-10 | UK | 25743 | 26593 | -850 |
| 2022 | 11 | 2022-11 | UK | 82248 | 88828 | -6580 |
| 2022 | 12 | 2022-12 | UK | 6856 | 7206 | -350 |
| 2023 | 1 | 2023-01 | UK | 95430 | 94094 | 1336 |
| 2023 | 2 | 2023-02 | UK | 40023 | 36461 | 3562 |
| 2023 | 3 | 2023-03 | UK | 61146 | 61329 | -183 |
| 2023 | 4 | 2023-04 | UK | 99505 | 95724 | 3781 |
| 2023 | 5 | 2023-05 | UK | 91095 | 94557 | -3462 |
| 2023 | 6 | 2023-06 | UK | 95013 | 101474 | -6461 |
| 2023 | 7 | 2023-07 | UK | 43351 | 39406 | 3945 |
| 2022 | 1 | 2022-01 | US | 59673 | 64566 | -4893 |
| 2022 | 2 | 2022-02 | US | 27674 | 25460 | 2214 |
| 2022 | 3 | 2022-03 | US | 15749 | 17214 | -1465 |
| 2022 | 4 | 2022-04 | US | 81957 | 80154 | 1803 |
| 2022 | 5 | 2022-05 | US | 48951 | 48266 | 685 |
| 2022 | 6 | 2022-06 | US | 37939 | 34449 | 3490 |
| 2022 | 7 | 2022-07 | US | 38285 | 41042 | -2757 |
| 2022 | 8 | 2022-08 | US | 81926 | 84056 | -2130 |
| 2022 | 9 | 2022-09 | US | 88508 | 90278 | -1770 |
| 2022 | 10 | 2022-10 | US | 26829 | 25890 | 939 |
| 2022 | 11 | 2022-11 | US | 79885 | 80604 | -719 |
| 2022 | 12 | 2022-12 | US | 73982 | 79087 | -5105 |
| 2023 | 1 | 2023-01 | US | 19263 | 18627 | 636 |
| 2023 | 2 | 2023-02 | US | 30409 | 30470 | -61 |
| 2023 | 3 | 2023-03 | US | 29834 | 31027 | -1193 |
| 2023 | 4 | 2023-04 | US | 54553 | 55590 | -1037 |
| 2023 | 5 | 2023-05 | US | 10118 | 9986 | 132 |
| 2023 | 6 | 2023-06 | US | 21799 | 20862 | 937 |
| 2023 | 7 | 2023-07 | US | 46333 | 44758 | 1575 |
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 🙂
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 🙂
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
