Forum Discussion
Previous Dynamic Period DAX Calculation
- 7 years ago
mark_carlisle seems like you have two logic going on, and you want if selection is full month then use full previous month.
so you can build a logic like, if first selected date is first date of the month and last selected date is last date of the month, full month is selected and then use this in if condition with PreviousMonth DAX function.
- 7 years ago
Yeah I figured that was going to be the case, was hoping there was going to be some time intelligence DAX function I might have missed. The following is what I've went with;
StartOfPreviousPeriod = IF ( AND ( FIRSTDATE ( CalendarCombined[Date] ) = STARTOFMONTH ( CalendarCombined[Date] ), LASTDATE ( CalendarCombined[Date] ) = ENDOFMONTH ( CalendarCombined[Date] ) ), FIRSTDATE ( PREVIOUSMONTH ( DATEADD ( CalendarCombined[Date], -1 * [MonthsInCurrentPeriod], MONTH ) ) ), FIRSTDATE ( PREVIOUSDAY ( DATEADD ( CalendarCombined[Date], -1 * [DaysInCurrentPeriod], DAY ) ) ) )
mark_carlisle seems like you have two logic going on, and you want if selection is full month then use full previous month.
so you can build a logic like, if first selected date is first date of the month and last selected date is last date of the month, full month is selected and then use this in if condition with PreviousMonth DAX function.
Yeah I figured that was going to be the case, was hoping there was going to be some time intelligence DAX function I might have missed. The following is what I've went with;
StartOfPreviousPeriod =
IF (
AND (
FIRSTDATE ( CalendarCombined[Date] ) = STARTOFMONTH ( CalendarCombined[Date] ),
LASTDATE ( CalendarCombined[Date] ) = ENDOFMONTH ( CalendarCombined[Date] )
),
FIRSTDATE (
PREVIOUSMONTH (
DATEADD ( CalendarCombined[Date], -1 * [MonthsInCurrentPeriod], MONTH )
)
),
FIRSTDATE (
PREVIOUSDAY (
DATEADD ( CalendarCombined[Date], -1 * [DaysInCurrentPeriod], DAY )
)
)
)