Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
George_Gian
Regular Visitor

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

08/01/2023GB011501
08/01/2023GB021041
08/01/2023IT014191
08/01/2023DE012951
08/01/2023FR014391
05/01/2023GB014322
05/01/2023GB022542
05/01/2023IT012762
05/01/2023DE014272
05/01/2023FR012192
31/12/2022GB011983
31/12/2022GB021903
31/12/2022IT014183
31/12/2022DE012323
31/12/2022FR013923

 

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

 

Dates Table

DateYearQuarterMonthWeekYear.Week

31/12/202220224Dec522022.52
01/01/202320231Jan12023.01
02/01/202320231Jan12023.01
03/01/202320231Jan12023.01
04/01/202320231Jan12023.01
05/01/202320231Jan12023.01
06/01/202320231Jan12023.01
07/01/202320231Jan12023.01
08/01/202320231Jan22023.02

 

Extraction Dates Ranking Table

Extraction DateIndex

08/01/20231
05/01/20232
31/12/20223

 

Market Country Table

Market ISO CodeMarket Country

GBUnited Kingdom
ITItaly
DEGermany
FRFrance

 

 

Market ISO Code Table

Market NodeMarket ISO Code

GB01GB
GB02GB
IT01IT
DE01DE
FR01FR

 

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

1 REPLY 1
George_Gian
Regular Visitor

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.