Forum Discussion
Cumulative Total % (Purchase vs Budget)
Trying to figure out a way to calculate the total % of purchases vs the budget.
We are supposed to maintain spending at the rate in which we have a budget for the year. Budget is $12,000 so we spent about $1000 a month or 8.3%.
On a line chart I have the X-axis as the months (July - June for fiscal year) and the Y axis is the spend rate in which we should go by about 8.3% per month). So July should be 8.3%, Aug should be 16.6%, sept 24.9%, etc.
If we buy $1000 in July, $800 in Aug and $900 in Jan, that is 22.5% and should reflect that rate, but it is only showing the 7.5% for that actual month.
I would like the budget % line to show linear from 0-100% on the chart and then the spent as a cumulative (8.3% in July, about 15% in Aug and 24.9% in Sept.
1 Reply
- AllisonKennedyCommunity Champion
PBInonpro , what have you tried to get the 7.5%?
If that measure is working correctly, let's say it's called [Budget Percent], then use this trick:
https://excelwithallison.blogspot.com/2023/11/dax-time-intelligence-easy-pattern-to.html
To create the running total:
YTD Budget Percent =
CALCULATE( [Budget Percent], DATESYTD( DimDate[Date], '06-30-) )