Cumulative SUM with filter

[jason435]

I have a budget table with budget amounts for the whole month with date value set to first of the month.

I would like to create a cumulative budget to compare against actual. A straight line from bottom left to top right of an area chart that will change based on a date slicer.

So I calculate the average budget revenue for one day in the period, then multiply by the number of days to date. 

I have the below formula. I know I am messing up which variables are calculated for each day, and which variable is fixed for each calculation. If that makes sense. 

Sum Budget to date = 
				var maxdate = MAX('Calendar'[Date])
				var mindate = MIN('Calendar'[Date])
				var revenuedays = maxdate - mindate +1
				var budgetdays = FORMAT(EOMONTH(maxdate,0)-mindate+1,0)
				RETURN
				CALCULATE(
					SUM(Budget[Budget Amount])/budgetdays*revenuedays,
					FILTER (
						ALLSELECTED ( 'Calendar'),
						'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
						&& 'Calendar'[Date] >= date(year(TODAY()),1,1)
))

[jason435]

Been doing some more research on this, I think I have a better grasp of what I need now. I think this is possible with a sumx.

ToDate Sum Budget v1 = 
			CALCULATE(
			SUMX(???))),
			FILTER (
			ALLSELECTED ( 'Calendar'),
			'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
			&& 'Calendar'[Date] >= date(year(TODAY()),1,1)
))	

This is for an area chart YTD or MTD chart, depending on the page filter.

I have a budget table with month and budget for each month. I added a calculated column with daily budget for each month.

For each date, I want to sum: (all budgets up to the end of the previous month) + mtd days * avg daily budget

for March 14 = Jan Budget + Feb Budget + 14 *Avg Mar Daily budget

for jan 26 = Avg Jan Daily Budget * 26

Result should look like the purple line, but with 'cleaner' line.

[jason435 · Accepted solution]

Ok, afte writing my previous post I had a duh moment and figured it out: If anyone has any ideas to make this more efficient please let me know.

ToDate Sum Budget = 
			var maxdate = MAX ( 'Calendar'[Date] ) 
			return
			CALCULATE(
			SUM(CRTBudget[Budget Amount]),
			FILTER (
			ALLSELECTED ( 'Calendar'),
			DATEADD('Calendar'[Date],1,MONTH) <= maxdate
			)) 
			+
		CALCULATE(
					sum(CRTBudget[Budget Amount Day])*
					(maxdate - date(year(maxdate),month(maxdate),1)+1),
					FILTER (
						ALLSELECTED ( 'Calendar'),
						month('Calendar'[Date]) = month( maxdate)
						))

[Ashish_Mathur]

Hi,

Share a dataset and show your expecte result.