topic Re: Dax Formula for calculated column for fiscal year in DAX Commands and Tips

Dax Formula for calculated column for fiscal year

SAP4026 — Fri, 16 Feb 2024 23:08:52 GMT

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:

Name	Board Start Date	Board End Date	FY20	FY21	FY23
Jane Doe	1/1/1979		Yes	Yes	Yes
John Doe	1/1/1990	10/15/2022	Yes	Yes	No
C. Dee	4/5/2023		No	No	Yes
F. Smith	5/30/2021	12/15/2021	No	No	No

Re: Dax Formula for calculated column for fiscal year

lbendlin — Sat, 17 Feb 2024 02:06:25 GMT

Not sure why you suppressed FY22 ? Bad year?

See attached.

Re: Dax Formula for calculated column for fiscal year

SAP4026 — Sat, 17 Feb 2024 21:12:51 GMT

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

Re: Dax Formula for calculated column for fiscal year

lbendlin — Sat, 17 Feb 2024 22:29:38 GMT

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.