Forum Discussion
Cumulative not totalling correctly
Hi,
I have the following measure that I want to calculate a cumulative total by grouping "month_group" based on app[app_date] (running 02 October to 01 October).
C_val
Calculate (
sum(app[value]),
Filter (
Allselected(app),
App[app_date] <= MAX app[app_date])),
Groupby (app, app[month_group]))
However, all this calculations does is give me the total value for the "month_group" groupings but doesn't cumulatively total this up.
Start Oct shows 2nd to 31st Oct. End Oct shows only value for 1st Oct.
Cumulative column shows the data how I'd expect to see it.
Month_group C_val Cumulative
Start Oct. 500. 500
Nov. 1000. 1500
Dec. 400. 1900
Jan. 100. 2000
Feb. 2000. 4000
Mar. 1000. 5000
Apr. 500. 5500
May. 500. 6000
June. 1000. 7000
Jul. 2000. 9000
Aug. 3000. 12000
Sep. 600. 12600
End Oct. 100. 12700
Any idea what I'd need to change my measure to for this to work please?
Hi M_SBS_6 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Adding indexed a column to a power query.
(3) We can create a measure.
Measure = CALCULATE(SUM('Table'[C_val]),FILTER(ALLSELECTED('Table'),'Table'[Index]<=MAX('Table'[Index])))(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
M_SBS_6 , Always prefer a date table in such case and use that in filter/slicer, measure and visual
A date/calendar table joined with date of your tableRunning Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.Calculate (
sum(app[value]),
Filter (
Allselected(Date),
Date[app_date] <= MAX (Date[app_date])) )
Hi M_SBS_6 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Adding indexed a column to a power query.
(3) We can create a measure.
Measure = CALCULATE(SUM('Table'[C_val]),FILTER(ALLSELECTED('Table'),'Table'[Index]<=MAX('Table'[Index])))(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
