cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maracles
Resolver II
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()))

var LeadsFiscalDayOfYear = CALCULATE(
	[Leads - # All],
	DATESYTD('Calendar'[Date],"30/09"),
	'Calendar'[Fiscal Day of Year] <= CurrFiscalDayOfYear)

Return LeadsFiscalDayOfYear 


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
maracles
Resolver II
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!

View solution in original post

3 REPLIES 3
maracles
Resolver II
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!

View solution in original post

Sean
Community Champion
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]) ) )

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. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors