Last corresponding date on previous month, [Doubt]

[bolabuga]

Hello everyone,

I need help in understanding this dax measure that creates a Previous month date column on a date table.

'Date'[PM Date] =
CALCULATE (
    MAX  ( 'Date'[Date] ),
    ALL ( 'Date' ),
    FILTER (
        ALL ( 'Date'[MonthDayNumber] ),
        'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )
            || EARLIER ( 'Date'[MonthDayNumber] ) = EARLIER ( 'Date'[MonthDays] )
    ),
    FILTER (
        ALL ( 'Date'[YearMonthNumber] ),
        'Date'[YearMonthNumber]
            = EARLIER ( 'Date'[YearMonthNumber] ) – 1
    )
)

The measure gets the "max" date on the previous month, however if you are on 28/february it will bring the last corresponding date 31/march, which is the expected result.

My question is, why does it work??

A) FILTER ( ALL ( 'Date'[MonthDayNumber] ), 'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] ) and   FILTER ( ALL ( 'Date'[YearMonthNumber] ), 'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) – 1

What i understand here: considering the row context in 02/28/2018, the date table will be filtered with all days <= 28 in january/2018 and the max(date) here would be 01/28/2018 (until here i think im understanding it right)

B) || EARLIER ( 'Date'[MonthDayNumber] ) = EARLIER ( 'Date'[MonthDays] ) --> 

On 02/27/2018 its bringing 01/27/2018, which, i think, is right, because earlier monthdaynumber is "27" which is different from earlier monthdays that is "28"

why does this line brings the day "01/31/2018" correctly on 02/28/2018??? In my row context the earlier monthdaynumber isnt "28"??? and the earlier monthdays isnt also "28"??? 

[v-yuta-msft]

@bolabuga ,

How about removing the two EARLIER functions? The result is same on my side.

'Date'[PM Date] =
CALCULATE (
    MAX  ( 'Date'[Date] ),
    ALL ( 'Date' ),
    FILTER (
        ALL ( 'Date'[MonthDayNumber] ),
        'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )
            || 'Date'[MonthDayNumber] ) = 'Date'[MonthDays]
    ),
    FILTER (
        ALL ( 'Date'[YearMonthNumber] ),
        'Date'[YearMonthNumber]
            = EARLIER ( 'Date'[YearMonthNumber] ) – 1
    )
)

If you still don't know why, please share the complete sample data.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bolabuga]

@v-yuta-msft 

Thks for the reply yuta,

Removing the 2 earliers will get wrong results when the previous month has fewer days than the current month. 

here is pbix file: https://drive.google.com/open?id=107_ZZR9RfeA5QXWOsoDnIYMbH9hT5Bpy

this "|| 'Date'[MonthDayNumber] ) = 'Date'[MonthDays]" is a true/false statement right??

When we are at 2/28/2018, and 28 (monthdaynumber) is equal to 28 (monthdays) we will have a "true" result, is that right??

the engine (im not sure engine is the right word here) will search for a "true" occurrence in the previous month, and then, will find 31 daynumber equals to 31 monthdays, and is this case returning the correct date, because the max (date) will be looking at a filtered table that contais all the days <= 28 plus 01/31/2018.

Im confusing things here, or that is whats happening??

[v-yuta-msft · Accepted solution]

@bolabuga ,

Actually there's some difference, I haven't make complete test.

[PM Date] measure is equal to dax below:

PM Date = 
CALCULATE (
    MAX  ( 'Date'[Date] ),
    ALL ( 'Date' ),
    FILTER (
        ALL ( 'Date'[MonthDayNumber] ),
        'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )
            || ('Date'[MonthDayNumber] = EARLIER ( 'Date'[MonthDays] ) && 'DATE'[monthdays] = EARLIER( 'DATE'[MonthDayNumber]) )
    ),
    FILTER (
        ALL ( 'Date'[YearMonthNumber] ),
        'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - 1
    )
)

And [PM Date without earlier] is equal to dax below:

PM Date (without earliers) = 
CALCULATE (
    MAX  ( 'Date'[Date] ),
    ALL ( 'Date' ),
    FILTER (
        ALL ( 'Date'[MonthDayNumber] ),
        'Date'[MonthDayNumber] <= EARLIER ( 'Date'[MonthDayNumber] )
            || 'Date'[MonthDayNumber] = EARLIER('Date'[MonthDays])
    ),
    FILTER (
        ALL ( 'Date'[YearMonthNumber] ),
        'Date'[YearMonthNumber] = EARLIER ( 'Date'[YearMonthNumber] ) - 1
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bolabuga]

@v-yuta-msft 

Hello yuta, none of those 2 codes are giving the right results when the previous month has more days than the current month.

The first returns the wrong date on all days.

The second code returns the wrong date on the last day of the current month.