Forum Discussion

nemo189's avatar
nemo189
Frequent Visitor
2 years ago

Flag for last year same period

I have a table with columns , End date period(end date of each period),Start date period(Start date of each period), Period (YYYY-YYYY PXX) .
I have created a measure to identify current year current period(2023-2024 P12) and current year last period(2023-2024 P11).
Current Year Last Period =
VAR MaxDate = MAXX(FILTER('Hold Date', 'Hold Date'[PERIOD_END_DT] + 1 < TODAY()), 'Hold Date'[Release Period])
VAR Year1 = LEFT(MaxDate, 4)
VAR Year2 = MID(MaxDate, 6, 4)
VAR Period = MID(MaxDate, 12, 2)
RETURN
Year1 & "-" & Year2 & " P" & Period
CurrentPeriod =
VAR CurrentDate = TODAY()
RETURN
MAXX(
FILTER(
'Hold Date',
'Hold Date'[PERIOD_START_DT] <= CurrentDate && 'Hold Date'[PERIOD_END_DT] >= CurrentDate
),
'Hold Date'[Release Period]
)

I want to create a flag to mark Current year Current Period as 1 till Last Year Current period.
the out put should be like this

PeriodFlag
2022-2023 P50
2022-2023 P60
2022-2023 P70
2022-2023 P80
2022-2023 P90
2022-2023 P100
2022-2023 P110
2022-2023 P121
2022-2023 P131
2023-2024 P11
2023-2024 P21
2023-2024 P31
2023-2024 P41
2023-2024 P51
2023-2024 P61
2023-2024 P71
2023-2024 P81
2023-2024 P91
2023-2024 P101
2023-2024 P111
2023-2024 P121
2023-2024 P130
2024-2025 P10





2 Replies

  • Have you considered using the SAMEPERIODLASTYEAR convenience function?

  • Hello nemo189,

     

    Can you please try this DAX:

    Period Flag = 
    VAR CurrentPeriod = 
        [CurrentPeriod]
    VAR LastYearCurrentPeriod =
        CALCULATE(
            [Current Year Last Period],
            DATEADD('Hold Date'[PERIOD_START_DT], -1, YEAR)
        )
    VAR CurrentPeriodDate = 
        MAX('Hold Date'[PERIOD_END_DT])comparison
    VAR IsWithinRange = 
        CurrentPeriodDate >= CALCULATE(MIN('Hold Date'[PERIOD_END_DT]), 'Hold Date'[Release Period] = LastYearCurrentPeriod)
        && CurrentPeriodDate <= CALCULATE(MAX('Hold Date'[PERIOD_END_DT]), 'Hold Date'[Release Period] = CurrentPeriod)
    RETURN
        IF(IsWithinRange, 1, 0)