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
SAP4026
Frequent Visitor

Dax Formula for calculated column for fiscal year

Is there a DAX formula that can get me the following: I want to create three (3) columns, named FY20, FY21, and FY23, based on a table that have board start dates and board end dates. The data in the FY20, FY21 and FY23 columns should be based on the board end dates that fall within the date ranges of FY20, FY21, and FY23. If there's no end date that means they're a current board member. My table does not have a definition for FY20, FY21, and FY23. I don't want to use a filter or slicer. 

However, this how how I define FY20 = 7/1/2019 to 6/30/2020, FY21 = 7/1/2020 to 6/30/2021, and FY 23 = 7/1/2022 to 6/30/2023

I'd like my final data to look like this:

NameBoard Start DateBoard End DateFY20FY21FY23
Jane Doe1/1/1979 YesYesYes
John Doe1/1/199010/15/2022YesYesNo
C. Dee4/5/2023 NoNoYes
F. Smith5/30/202112/15/2021NoNoNo
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Not sure why you suppressed FY22 ?  Bad year?

 

lbendlin_0-1708135552945.png

See attached.

 

 

View solution in original post

3 REPLIES 3
SAP4026
Frequent Visitor

@lbendlin , thank you. How did you create the Dates table with the date ranges for the fiscal years?

You can see that from the Calculated Table definition.  I used EDATE to shift the months as needed. 

 

NOTE: This was just for the solution proposal. In real life I would never do that. I would use an external reference table where everything is precomputed.

lbendlin
Super User
Super User

Not sure why you suppressed FY22 ?  Bad year?

 

lbendlin_0-1708135552945.png

See attached.

 

 

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