Calculate MOM based on DateKey

[abhiram342]

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:

DateKey	Product	Sales
20220101	A	100
20220102	B	200
…
20220201	A	300
20220202	B	400

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

DateKey	Date
20220101	1/1/2022
20220102	1/2/2022
…..
20220201	2/1/2022
20220202	2/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

[amitchandak]

@abhiram342 , Date table has column that is date, which you have used to mark it as date table use that

Assuming date is joined with date table

Last Month = CALCULATE(SUM('FactSales'[Sales]), dateadd('DAte'[Date],-1, Month )

We should use date from date table.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA