MoM % by day, handling end of month day difference

[Anonymous]

I have a calculation for Month over Month Percentage seen below. This uses QuantityShipped for Yesterday this month and last month. My issue is when we get to the end of the months. I need to take into account that the month ends don't always match. So, if I need to compare "current month" October and "prior month" September, on October 31 I'll have an issue because September doesn't have 31 days. On this day, it needs to take total MTD Quantity Shipped for September 1-30 and October 1-31. And for March to February, it would need to occur multiple times, starting March 29 it would be MTD Feb 1-28 and March 1-29, then Feb 1-28 and March 1-30, and finally Feb 1-28 and March 1-31 to finish off the month.

How can I make sure all the date differences are accounted for without messing up how it works currently? I still would want October 30 and September 30 to compare just the single day. Nothing should change until we get to a day in a month where the previous month day doesn't exist.

MoMPct = ([CurrentMoM]-[PriorMoM])/[PriorMoM]

CurrentMoM = CALCULATE(SUM(factSalesTPD[QuantityShipped]),filter('Date','Date'[Date]=today()-1 ))

PriorMoM = 

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 ( factSalesTPD[QuantityShipped]),
FILTER (
'Date',
'Date'[Date]
= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () - 1 ) )
)
)

[MFelix]

Hi @Anonymous,

Use the TOTALMTD to make the calculations:

Current month = TOTALMTD(SUM(Table[Quantities]); Date[Date])

Pryor month = TOTALMTD(SUM(Table[Quantities]); DATEADD(Date[Date]; -1; Monht))

This should give you the total values for MTD

Regards.

MFelix

[Anonymous]

Thanks, But I need this logic to all be bundled into one calculation so that it's checking (based on current date). So I guess I'd need some kind of IF statement? I haven't figured out that logic yet.

[v-chuncz-msft]

@Anonymous,

You may try the expression below.

VAR d =
    TODAY ()
RETURN
    IF ( DAY ( DATE ( YEAR ( d ), MONTH ( d ) - 1, DAY ( d ) ) ) = DAY ( d ), 0, 1 )

[Anonymous]

@v-chuncz-msft

I'm not sure I understand. What does this break down to mean? And where do I add this into my current calculations?