cancel
Showing results for
Did you mean:
Resolver II

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

DATESYTD('Calendar'[Date],"30/09"),
'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.

Any suggestions?

1 ACCEPTED SOLUTION
Resolver II

Managed to solve this myself (for once). Turns out I was overthinking this, in LeadsFiscalDayOfYear I simply had the filter condition be Calendar[Date] <= Today().

Wasted way to long on that one!

3 REPLIES 3
Resolver II

Managed to solve this myself (for once). Turns out I was overthinking this, in LeadsFiscalDayOfYear I simply had the filter condition be Calendar[Date] <= Today().

Wasted way to long on that one!

Community Champion

@maracles Try these... Let me know if they work and do you get same results?

```Total FY = CALCULATE(SUM(Table1[Amount]), DATESBETWEEN('Calendar'[Date], STARTOFYEAR('Calendar'[Date], "6/30"), ENDOFYEAR('Calendar'[Date], "6/30") ) )

Running Total FY = CALCULATE([Total FY], FILTER(ALL('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date]) ) )```
Resolver II

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.

Thanks.

Announcements