Calculating Cumulative Total for Fiscal Day of Year
I'm trying to create a cumulative total based on Fiscal Day of Year.
I am trying to use this measure:
Leads - FYTD Day =
var CurrFiscalDayOfYear = CALCULATE(MAX('Calendar'[Fiscal Day of Year]), FILTER('Calendar','Calendar'[Date]=TODAY()))
var LeadsFiscalDayOfYear = CALCULATE(
[Leads - # All],
'Calendar'[Fiscal Day of Year] <= CurrFiscalDayOfYear)
This does not work as it returns a single value. If however I replace CurrFiscalDayOfYear with a hard coded integer 203 then it works.
I have therefore deduced that the issue is the aggregation using MAX() in CurrFiscalDayOfYear however I can't figure out how to replace it.
In my date table I have a custom column called 'Fiscal Day of Year', I'm trying to use this to identify the current day and limit my cumulative total.
Thanks Sean, I should have mentioned that I need my solution to work when displayed over a date range. I am using a line chart and need it to appear cumulative across what ever date range I put on the x axis i.e. days, weeks, months, quarters.
Your solution only seems to give the max number based on current day of the year (which does work).
You have given me some ideas to try and clean up my solution though as it wasn't perfect.