Forum Discussion
Last corresponding date on previous month, [Doubt]
- 7 years ago
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 ,
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.
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.