cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## 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
Community Support

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

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.

Helper V

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

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.

Helper V

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

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors