Time Calculation based on slicer

George_Gian — Fri, 27 Jan 2023 14:23:14 GMT

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

Re: Time Calculation based on slicer

George_Gian — Fri, 27 Jan 2023 14:28:21 GMT

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

topic Time Calculation based on slicer in DAX Commands and Tips

Time Calculation based on slicer

Re: Time Calculation based on slicer