cancel
Showing results for
Search instead for
Did you mean:
Member

Last corresponding date on previous month, [Doubt]

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"???

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

Re: Last corresponding date on previous month, [Doubt]

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.

4 REPLIES 4
Community Support Team

Re: Last corresponding date on previous month, [Doubt]

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.

Member

Re: Last corresponding date on previous month, [Doubt]

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??

Highlighted
Community Support Team

Re: Last corresponding date on previous month, [Doubt]

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.

Member

Re: Last corresponding date on previous month, [Doubt]

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.

Helpful resources

Announcements

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)