Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nemo189
Frequent Visitor

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 2
Sahir_Maharaj
Super User
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)

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
lbendlin
Super User
Super User

Have you considered using the SAMEPERIODLASTYEAR convenience function?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors