Forum Discussion
Helper VLast corresponding date on previous month, [Doubt]
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.
Community Supportbolabuga ,
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 VThks 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
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
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.