compare current MTD to last month but for the same amount of day

[SwathiKundar]

Hello All,

So we want to compare current MTD to last month but for the same amount of days, so it would compare current MTD(4/1/2021-4/28/2021) to the same time frame of last month (3/1/2021-3/28/2021).

Here I wrote dax like this,

Revenue LM = CALCULATE(SUM(table[Revenue]), PREVIOUSMONTH(Dates[Date])) but this will returns previous month complete data but I need to display till (3/1/2021-3/28/2021). Can anyone help with the solution?

Thanks in advance.

[daxer-almighty]

OK... What happens when the current month has 31 days and the previous one has 30? If you happen to be on the 31st and calculate MTD, you won't get 31 days from the month before. What do you want to do in such a situation? Also, in Feb you've got only 28 days, so what happens when you're on 29th, 30th, 31st of March?

// Please note that for this to work
// the Dates table must be a true date
// table marked as such in the model.


[Revenue MTD LM (Exact)] =
// Dates[DayNumberOfMonth] goes from 1 through to
// the last day of the month, from 1 thru to 28/29/30/31.
var LastVisibleDayNumberOfMonth = MAX( Dates[DayNumberOfMonth] )
var LastVisibleMonthID = MAX( Dates[MonthID] )
var PreviousMonthDaysOfInterest =
    CALCULATETABLE(
        DISTINCT( Dates[Date] ),
        // Dates must also have a column that stores
        // a sequence numbering each month in the table.
        // Starting from 1 or 0 and going up without gaps.
        // This sequence is a unique identifier for all the
        // months across all years, so it's NOT a number
        // from 1 to 12. It goes up by 1 each time a new
        // month is encountered. Call such a column MonthID.
        Dates[MonthID] = LastVisibleMonthID - 1,
        Dates[DayNumberOfMonth] <= LastVisibleDayNumberOfMonth,
        ALL( Dates )
    )
var PreviousMonthHasEnoughDays = 
    COUNTROWS( PreviousMonthDaysOfInterest )
        = LastVisibleDayNumberOfMonth
var Result =
    IF( PreviousMonthHasEnoughDays,
        // So, this measure will show up only when
        // there's enough days in the month before.
        CALCULATE(
            [Revenue], // this should be the base measure
            PreviousMonthDaysOfInterest
        )
    )
RETURN
    Result

[v-shex-msft · Accepted solution]

HI @SwathiKundar,

You can try to use the date function to manually define the filter range for calculating.

Revenue LM =
VAR _list =
    VALUES ( Dates[Date] )
VAR _min =
    MINX ( _list, [Date] )
VAR _max =
    MAXX ( _list, [Date] )
RETURN
    CALCULATE (
        SUM ( table[Revenue] ),
        FILTER (
            ALLSELECTED ( table ),
            [Date]
                >= DATE ( YEAR ( _min ), MONTH ( _min ) - 1, DAY ( _min ) )
                && [Date]
                    <= DATE ( YEAR ( _max ), MONTH ( _max ) - 1, DAY ( _max ) )
        )
    )

Regards,
Xiaoxin Sheng