Filtering by fiscal year for sum

[kman42]

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()))

[kman42]

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.

[gmsamborn]

Hi @kman42 

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.