Forum Discussion
Fiscal Year Calculated Columns from date ranges
I'm creating a date table and I've used the Calendar() function as the basis. So, I have a Date column in the Dates table, as well as other calculated columns for Month, Quarter, etc. I want to add an additional calculated column for Fiscal Year, based on start and end dates defined in another table in my model.
I have a GLYEAR table that has the following structure:
Date Start_Date
Date End_Date
int FRL_FISCAL_YEAR
Basically the psuedo code for the calculated colum should be "If Dates[Date] >= GLYEAR[START_DATE] and Dates[Date] <= GLYEAR[START_DATE] then FRL_FISCAL_YEAR
There is no existing relationship between Dates and GLYEAR.
Appreciate any pointers.
This seems like it's giving me the results I need. Does anyone see any issues with this solution?
Fiscal Year = CALCULATE(VALUES(GLYEAR[FISCAL_YEAR]), FILTER(GLYEAR, Dates[Date] >= GLYEAR[START_DATE] && Dates[Date] <= GLYEAR[END_DATE]))
2 Replies
- ChrisWilliamsAdvocate II
This seems like it's giving me the results I need. Does anyone see any issues with this solution?
Fiscal Year = CALCULATE(VALUES(GLYEAR[FISCAL_YEAR]), FILTER(GLYEAR, Dates[Date] >= GLYEAR[START_DATE] && Dates[Date] <= GLYEAR[END_DATE]))
- v-ljerr-msftMicrosoft Employee
Hi ChrisWilliams,
According to your description, the formula provided above should work in this scenario.:smileyhappy:
In addition, could you accept your reply as solution to help others who has similar issue easily find the answer and close this thread?
Regards