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

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

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
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.