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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.