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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jo123456
Helper I
Helper I

DAX formula help

My formula works right now but I want to know if it will always give a valid output or potentially a simplier version?
 
 
YearIndicator = if(
            (('D-DATE'[CalendarYear]*100)+'D-DATE'[CalendarMonthNum])
            <((YEAR(TODAY())-1)*100)+9,"Before Previous Year",
if(
            (('D-DATE'[CalendarYear]*100)+'D-DATE'[CalendarMonthNum])
             >=((YEAR(TODAY())-1)*100)+9
            && ((('D-DATE'[CalendarYear]*100)+'D-DATE'[CalendarMonthNum])
            <=(YEAR(TODAY())*100)+8),"Previous Year",
IF (
            (('D-DATE'[CalendarYear] * 100) + 'D-DATE'[CalendarMonthNum])
            >= ((YEAR(TODAY()) * 100) + 9)
            && (('D-DATE'[CalendarYear] * 100) + 'D-DATE'[CalendarMonthNum])
               <= ((YEAR(TODAY()) + 1) * 100) + 8,"Current Year",
IF (
            (('D-DATE'[CalendarYear] * 100) + 'D-DATE'[CalendarMonthNum])
            >= ((YEAR(TODAY()) + 1) * 100) + 9
            && (('D-DATE'[CalendarYear] * 100) + 'D-DATE'[CalendarMonthNum])
               <= ((YEAR(TODAY()) + 2) * 100) + 8,
            "Next Year",
"Future Dates"))))
3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

@jo123456 Here is a cleaned up version using SWITCH TRUE. Is the goal here to essentially implement a fiscal year flag where the fiscal year starts in September? 

YearIndicator1 = 
    SWITCH( TRUE(), 
        'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] < (YEAR(TODAY())-1) * 100 + 9, "Before Previous Year",
        'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] >=(YEAR(TODAY())-1) *100 + 9 && 'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] <= YEAR(TODAY()) * 100 + 8, "Previous Year",
        'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] >= YEAR(TODAY()) * 100 + 9 && 'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] <= (YEAR(TODAY()) + 1) * 100 + 8, "Current Year",
        'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] >= (YEAR(TODAY()) + 1) * 100 + 9 && 'D-DATE'[CalendarYear] * 100 + 'D-DATE'[CalendarMonthNum] <= (YEAR(TODAY()) + 2) * 100 + 8, "Next Year",
        "Future Dates"
    )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

starts in september of the current year and always ends in august of the next year

Greg_Deckler
Community Champion
Community Champion

@jo123456 Does seem possible that there should be an easier way to do this. One thing that would help greatly would be to use SWITCH( TRUE(), ... ) instead of the nested IF statements.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.