Cumulative not totalling correctly

[M_SBS_6]

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? 

[Anonymous · Accepted solution]

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. 

[amitchandak]

@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 table

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue 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-bd52912a5b...
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])) )