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.
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.
Solved! Go to Solution.
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]))
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]))
Hi @ChrisWilliams,
According to your description, the formula provided above should work in this scenario.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |