Forum Discussion

mark_carlisle's avatar
mark_carlisle
Advocate IV
7 years ago
Solved

Previous Dynamic Period DAX Calculation

I've followed the guide on https://radacad.com/previous-dynamic-period-dax-calculation for creating a dynamic previous period. So if I select a date range for the current period of;   01/06/2019 t...
  • parry2k's avatar
    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.

  • mark_carlisle's avatar
    mark_carlisle
    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 )
            )
        )
    )