TOTALMTD custom dates

[JDro]

Hello,

I'm trying to calculate TOTALMTD for a field but our monthly 'dates' dont align perfectly with a natural calendar date, e.g. what we call "Month 2" rather than simply being February actually comprises all dates from 30/01/2020 to 26/02/2020, I have a dates table which links every natural date to what we call each month internally (CustomMonth).

Is there a way to use TOTALMTD but have it work with my field "CustomMonth" rather than the natural date field? Alternatively another way to get cumulative values from the start of the current "custommonth" until today()? But have it unaffected by slicers/filters on the visual in the same way TOTALMTD is unaffected

I tried to use the following cumulative totals formula which does work with my customMonth field, but filtering to say week 3 of the month shows a value just for that week rather than for the customMonth to week 3:

PTD Sales = Calculate(

sum(Table1[Sales]),

FILTER(

ALLSELECTED(Dates),Dates[Date] <= MAX(Dates[Date])

)

)

The above code is obviously affected by my CustomMonth selections on the visual, whereas I need them implicately limited to the current CustomMonth regardless of visual slices if that makes sense.

Many thanks

[amitchandak · Accepted solution]

@JDro , Create a column like  // Assuming you have Month Start date

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1

and measures like

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))

[JDro]

Hey Amit,

This worked for me, thanks! I can't quite follow what your code is doing though, in the interest of learning the why, is there a simplistic explanation for what those columns and measures are doing please?

[mahoneypat]

Please try this expression instead

PTD Sales =
VAR vThisCustomMonth =
    MIN ( Dates[CustomMonth] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALLSELECTED ( Dates ),
            Dates[Date]
                <= MAX ( Dates[Date] )
        ),
        Dates[CustomDate] = vThisCustomMonth
    )

Or, if your visual includes your CustomMonth column, you can just use

PTD Sales = Calculate(

sum(Table1[Sales]),

FILTER(

ALLSELECTED(Dates[Date]),Dates[Date] <= MAX(Dates[Date])

)

)

Regards,

Pat

[JDro]

Hi Pat,

What is the CustomDate field you refer to here? my CustomMonth or something else?

thanks