Hello Community,
Hope you are well.
I would appreciate your help with the below time calculation question.
I have the below tables and I want to :
- calculate the delta of quantities between the latest extraction date versus its previous one
- calculate the delta of quantities between the latest extraction date versus the one before its previous one
- demonstrate the results with the use of a date slicer
The 3rd point is the one causing me headaches. I know that, upon selecting a date at the slicer, this filtering is cascaded to the Fact table, eventually destroying the delta calculations, as the only visible rows are the ones whose extraction dates are equal to the selected slicer date.
I managed to find a workaround (please see below), however this approach doesn't work at all when such as slicer is in place.
Any help would be massively welcomed 🙂
My approach is this:
Tables:
Fact Table
Extraction DateMarket NodeQuantityIndex
| 08/01/2023 | GB01 | 150 | 1 |
| 08/01/2023 | GB02 | 104 | 1 |
| 08/01/2023 | IT01 | 419 | 1 |
| 08/01/2023 | DE01 | 295 | 1 |
| 08/01/2023 | FR01 | 439 | 1 |
| 05/01/2023 | GB01 | 432 | 2 |
| 05/01/2023 | GB02 | 254 | 2 |
| 05/01/2023 | IT01 | 276 | 2 |
| 05/01/2023 | DE01 | 427 | 2 |
| 05/01/2023 | FR01 | 219 | 2 |
| 31/12/2022 | GB01 | 198 | 3 |
| 31/12/2022 | GB02 | 190 | 3 |
| 31/12/2022 | IT01 | 418 | 3 |
| 31/12/2022 | DE01 | 232 | 3 |
| 31/12/2022 | FR01 | 392 | 3 |
The Index column is the result of a merge, described below.
Dates Table
DateYearQuarterMonthWeekYear.Week
| 31/12/2022 | 2022 | 4 | Dec | 52 | 2022.52 |
| 01/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 02/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 03/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 04/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 05/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 06/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 07/01/2023 | 2023 | 1 | Jan | 1 | 2023.01 |
| 08/01/2023 | 2023 | 1 | Jan | 2 | 2023.02 |
Extraction Dates Ranking Table
Extraction DateIndex
| 08/01/2023 | 1 |
| 05/01/2023 | 2 |
| 31/12/2022 | 3 |
Market Country Table
Market ISO CodeMarket Country
| GB | United Kingdom |
| IT | Italy |
| DE | Germany |
| FR | France |
Market ISO Code Table
Market NodeMarket ISO Code
| GB01 | GB |
| GB02 | GB |
| IT01 | IT |
| DE01 | DE |
| FR01 | FR |
Relationships:
Dates[Date] 1-* Fact{Extraction Date]
Market ISO Code[Market Node] 1-* Fact[Market Node]
Market Country[Market ISO Code] 1-* Market ISO Code[Market ISO Code]
Merges:
I have merged the Fact and Extraction Dates Ranking tables, via the Extraction Date columns, and inserted the Extraction Date column at the Fact table
Measures:
Qty=SUM('Fact'[Quantity])
Qty_Week_1=CALCULATE([Qty],'Fact'[Index]=1)
Qty_Week_2=CALCULATE([Qty],'Fact'[Index]=2)
Qty_Week_3=CALCULATE([Qty],'Fact'[Index]=3)
Delta_Week_1_vs_2=[Qty_Week_1] - [Qty_Week_2]
Delta_Week_1_vs_3=[Qty_Week_1] - [Qty_Week_3]
Thank you,
George
