Forum Discussion
Budget vs actuals Combo Visual
Im trying to create a visual that will show cumulative actuals by month. I want it to look like the below but instead of each month showing the cumulative total, I want to show the actuals for that month and the months prior. So, January would show January actuals, February would show Jan+Feb.
cbuckne1 ,
In calculate column, the expression should be as below:
Chart Value = CALCULATE(SUM('Table'[Actuals]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))In measure, the expression should be as below:
Chart Value Measure = CALCULATE(SUM('Table'[Actuals]), FILTER(ALL('Table'), COUNTROWS(FILTER('Table', 'Table'[Index] >= EARLIER('Table'[Index])))))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Ensure you have a Calendar Table with Year and Month columns. Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table. To your visual, drag Year and Month from the Calendar Table. Write these measures
Total sales = SUM(Data[Sales])
Cumulative sales = CALCULATE([Total sales],DATESYTD(Calendar[Date],"31/12"))
Hope this helps.
5 Replies
- vivran22
Community Champion
Hello cbuckne1
Do you need a running total as and when the month progresses or just total of current month & previous month?
In case if it is later, then you may use following measure in your visual:
Prev Month Actual = CALCULATE( SUM(dtTable[Actual]), PREVIOUSMONTH(dtTable[Month]) ) +SUM(dtTable[Actual])Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedInIm looking for a running total.
In the most simplistic of terms the below is what Im looking for.
v-yuta-msftCommunity Support
cbuckne1 ,
In calculate column, the expression should be as below:
Chart Value = CALCULATE(SUM('Table'[Actuals]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))In measure, the expression should be as below:
Chart Value Measure = CALCULATE(SUM('Table'[Actuals]), FILTER(ALL('Table'), COUNTROWS(FILTER('Table', 'Table'[Index] >= EARLIER('Table'[Index])))))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-yuta-msft
cbuckne1 ,
It seems like your requirement is to achieve the running total value, right? If you could share sample data via onedrive for business and give the expected result, I could do further analysis.
Regards,
Jimmy Tao
