Forum Discussion
Filtering by fiscal year for sum
I'm having trouble getting this measure to calculate:
Sum of Split_DC yr 1 = CALCULATE(SUM('Budget splits'[Split_DC]), FILTER('award',YEAR(award[award_begin_date]) = (LOOKUPVALUE(CalendarAwardStartDate[FY],CalendarAwardStartDate[Date],TODAY()))))
I think there is an issue with the formats of the two years being compared. I think YEAR() returns a string and [FY] is a whole number.
If I do the following it works fine, but this simplified measure doesn't use the fiscal year.
Sum of Split_DC yr 1 = CALCULATE(SUM('Budget splits'[Split_DC]), FILTER('award',YEAR(award[award_begin_date]) = YEAR(TODAY()))
3 Replies
- gmsamborn
Super User
Hi Anonymous
It does have to do with types but YEAR() returns an integer (ie. 2023) whereas 'CalendarAwardStartDate'[Date] returns a date. You could change it to YEAR('CalendarAwardStartDate'[Date]).
If possible, I would really reccommend using a date table (that is related to your fact table) that has your Fiscal Year, Quarter, and Period (month) calculated for you.
Date Dimension Setup using M script.zip
Let me know if you have any questions.
I do have a date table (CalendarAwardStartDate) that has a fiscal year column. Maybe I'm just not sure how to use it in this instance. I want a measure that sums Split_DC for all awards in a particular fiscal year, but I don't want to hard code the fiscal year. I'm trying to create three measures, one each for this year, next year, and the year after so I can put them in a table. Any help would be appreciated.
- gmsamborn
If that is your date table, isn't it related to your fact table? I'm not sure why you are using LOOKUPVALUE.
