Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 :
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
Please allow me a correction at the merge step.
Merges:
I have merged the Fact and Extraction Dates Ranking tables, via the Extraction Date columns, and inserted the Index column at the Fact table
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
12 | |
10 | |
6 |