MOM Calculation based on Last Day of Month

[abhiram342]

Hi Team,

We are storing last day of month as key in our fact tables. We need to calculate mom but I see the previous month value is coming as blank because it doesn't fall on last day of month. 

Exmaple:

For April 2022, Lasy day is 2022-04-30 when we use dateadd(month,-1) then it falls on march 30 and It's expected to be 20220331.

Please suggest how to resolve it

As per our design, we have below logic

Is Latest (( we keep track to latest date from Fact)

Latest Sales Date Key

20220430

Date (( Standard Date Tables)

FactSales

DateId	SalesAmount
20220430	200
20220331	300

DAX Code:

PrevLatest is not working as expected

'Fact Sales'[Sales] = CALCULATE(SUM('Fact Sales'[Sales Amount]))

Sales MoM:=

VAR Curr = 'Fact Sales'[Sales]

VAR Prev =

    CALCULATE ( 'Fact Sales'[Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

VAR LatestDateKey =

    MAX ( 'Is Latest'[Latest Sales Date Key] )

VAR LatestDate =

    CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[Date Key] = LatestDateKey )

VAR PrevLatest =

    CALCULATE (

        'Fact Sales'[Sales]

        DATEADD ( TREATAS ( { LatestDate }, 'Date'[Date] ), -1, MONTH )

    )

VAR CurrMOM =

    IF ( ISBLANK ( Curr ), BLANK (), Curr - Prev )

VAR LatestMOM =

    IF ( ISBLANK ( Curr ), BLANK (), Curr - PrevLatest )

RETURN

    IF ( ISCROSSFILTERED ( 'Date'[Date] ), CurrMOM, LatestMOM)

Thanks,

Abhiram

[amitchandak · Accepted solution]

@abhiram342 , You are storing data only on one date , then all last month formula should work

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

if you only want the last month-end date

Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[date]=eomonth(max('Date'[Date]),-1) ))

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.

[abhiram342]

Thanks @amitchandak ! thank you for you solution. I have update below code as workaround. Please let me know if there are alternative ways

VAR PrevLatest =
CALCULATE (
'Fact Sales'[Sales],
'Date'[Date] = EOMONTH(EDATE(LatestDate, -1),0))
)

Thanks,

Abhiram