DAX help with Dateadd and TODAY()-1 for MoM calculation

[Anonymous]

Hello,

I'm trying to create a calculation that finds Month over Month % as:  (Current MoM - Prior MoM) / Prior MoM

Current MoM calculation is: CALCULATE(SUM(factSales[QuantityShipped]),filter('Date','Date'[Date]=today()-1 )) which gives me the Quantity Shipped amount for Nov 19.

I need Prior MoM to be the Quantity Shipped amount for Oct 19, so I was wanting the filter to be something like filter('Date','Date'[Date]=dateadd(today()-1,-1,MONTH ))  but that has an issue with today()

I also need to account for end of month dates not matching up, but that can be step 2.

How can I fix the Prior MoM calculation?

[MFelix · Accepted solution]

Hi @Anonymous,

The DATEADD function only accepts one column as parameter try the following measure I have already made it to work with change of year.

PMOM =
VAR Date_select =
    IF (
        MONTH ( TODAY () ) = 1;
        DATE ( YEAR ( TODAY () ) - 1; 12; DAY ( TODAY () - 1 ) );
        DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
    )
RETURN
    CALCULATE (
        SUM ( Table1[sales] );
        FILTER (
            'Date';
            'Date'[Date]
                = DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 1; DAY ( TODAY () - 1 ) )
        )
    )

Regards,

MFelix

[tjlee]

Try this formula

EOMONTH(TODAY(),-1)