cancel
Showing results 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
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
Highlighted
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]

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

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

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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,190)