Forum Discussion

jason435's avatar
jason435
Icon for Helper II rankHelper II
8 years ago
Solved

Cumulative SUM with filter

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

 

Need Help
  • jason435's avatar
    jason435
    8 years ago

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

     

     

3 Replies

  • jason435's avatar
    jason435
    Icon for Helper II rankHelper II
    8 years ago

    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's avatar
      jason435
      Icon for Helper II rankHelper II
      8 years ago

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