Forum Discussion
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
| Period | Flag |
| 2022-2023 P5 | 0 |
| 2022-2023 P6 | 0 |
| 2022-2023 P7 | 0 |
| 2022-2023 P8 | 0 |
| 2022-2023 P9 | 0 |
| 2022-2023 P10 | 0 |
| 2022-2023 P11 | 0 |
| 2022-2023 P12 | 1 |
| 2022-2023 P13 | 1 |
| 2023-2024 P1 | 1 |
| 2023-2024 P2 | 1 |
| 2023-2024 P3 | 1 |
| 2023-2024 P4 | 1 |
| 2023-2024 P5 | 1 |
| 2023-2024 P6 | 1 |
| 2023-2024 P7 | 1 |
| 2023-2024 P8 | 1 |
| 2023-2024 P9 | 1 |
| 2023-2024 P10 | 1 |
| 2023-2024 P11 | 1 |
| 2023-2024 P12 | 1 |
| 2023-2024 P13 | 0 |
| 2024-2025 P1 | 0 |
2 Replies
- lbendlin
Super User
Have you considered using the SAMEPERIODLASTYEAR convenience function?
- Sahir_Maharaj
Super User
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)