Forum Discussion

abhiram342's avatar
abhiram342
Microsoft Employee
4 years ago

Calculate MOM based on DateKey

Hi All,

 

I have removed Timestamp (Date) columns and using DateKeys in Fact Tables .  I need to  update few measures from Date to  DateKey. I have below measure (MOM) but facing issue while using DateKey. Is there any workaround (( I need to convert yyyyMMdd to Date while adding to DateAdd Function)

[Total Sales] = CAlCULATE(SUM('FactSales'[Sales]))
MOM Sales : (CALCULATE([Total Sales],ALL('Is Latest'),TREATAS(VALUES('Is Latest'[FactSalesLatestDateKey]),'Date'[DateKey]))/(CALCULATE([Total Sales],ALL('Is Latest'),DATEADD(TREATAS(VALUES('Is Latest'[FactSalesLatestDateKey]),'Date'[DateKey]),-1,MONTH))))-1

 

Note: We keep 2 months daily data and remaining months as monhtly data ( aggregate daily data to single day( last day of month)) in FactSales

 

FactSales:

 

DateKeyProductSales
20220101A100
20220102B200
  
20220201A300
20220202B400

Date: Marked as Date Table ( Date Column as UniqueIdentifier)

DateKeyDate
202201011/1/2022
202201021/2/2022
….. 
202202012/1/2022
202202022/2/2022

Is Latest: this Table tracks Latest datekey in fact tables

Is Latest
FactSalesLatestDateKey
20220202

Relationships:

FactSales * (DateKey) --> 1 Date (DateKey)

FactSales * (DateKey) --> IsLatest(FactSalesLatestDateKey)

 

 

Thanks,

Abhiram

1 Reply