Time Calculation based on slicer
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