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 :

1. calculate the delta of quantities between the latest extraction date versus its previous one
2. calculate the delta of quantities between the latest extraction date versus the one before its previous one
3. 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

The Index column is the result of a merge, described below.

Dates Table

DateYearQuarterMonthWeekYear.Week

Extraction Dates Ranking Table

Extraction DateIndex

Market Country Table

Market ISO CodeMarket Country

Market ISO Code Table

Market NodeMarket ISO Code

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