cancel
Showing results for
Search instead for
Did you mean:
Helper I

## Create a cumulative sum for a measure

Hi guys,

I have a simple measure based on two other measures.

Measures  = ( [PR % FC] * [Vol. MSU FC] )
PR % FC = CALCULATE (AVERAGE('Consolidated'[Value]), 'Consolidated'[Scorecard]="PR % FC")
Vol. MSU FC = CALCULATE(AVERAGE('Consolidated'[Value]), 'Consolidated'[Scorecard]="Vol. MSU FC")

The table "Consolidated" is like this:

Now, I visualized using table, but it's not accummulated.

What I want is like this, accummulated:
 Year Quarter Month Measures 2019 Qtr 3 July 857.46 2019 Qtr 3 August 1502.83 2019 Qtr 3 September 2143.54 2019 Qtr 4 October 2819.25 2019 Qtr 4 November 3484.40 2019 Qtr 4 December 4254.06

Can you help me on this?

THank you!

3 ACCEPTED SOLUTIONS
Community Support

Hi @akbjf ,

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I

@akbjf  First remove date hierarchy

then create a measure:

``````Measure2 = SUMX (
FILTER (
ALLSELECTED ( data1[Date] ),
data1[Date] <= MAX ( data1[Date] )
),
[Measure]
)``````

Super User

TRy the below measure:

Measure 2 = SUMX(FILTER(ALL(data1[Date]),data1[Date]<=MAX(data1[Date])),[Measure])

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
18 REPLIES 18
Super User

@akbjf , you can done with help from a date calendar

Example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Appreciate your Kudos.

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
Resolver I

Try this:

CumulativeSum =

CALCULATE(

[Measures)],

FILTER(

ALLSELECTED('YourTable'[Month]),

ISONORAFTER('YourTable' [Month], MAX('YourTable' [Month]), DESC)

)

)

Super User

Try the below DAX for cumlative sum:

MEASURE = CALCULATE(( [PR % FC] * [Vol. MSU FC] ),FILTER(ALL(Table),Table[DateCOlumn]<=MAX(Table[DateCOlumn])))

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Community Champion

Hi @akbjf

Try something like.

``````Measure =
VAR __maxDate = MAX( Dates[Date] )
RETURN
CALCULATE(
SUMX( VALUES( dates[YearMonthColumn] ),  [PR % FC] * [Vol. MSU FC] ),
ALL( Dates ),
Dates[Date] <= __maxDate
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Helper I

Hi Mariusz!

THank you for your suggestion.

I tried your formula, but it only accummulates at the end of series.

 Year Quarter Month Measures Measure Mariusz Should be like this 2019 Qtr 3 July 857.5 857.50 857.50 2019 Qtr 3 August 645.4 645.40 1502.90 2019 Qtr 3 September 640.71 640.71 2143.62 2019 Qtr 4 October 675.69 675.69 2819.31 2019 Qtr 4 November 665.13 665.13 3484.44 2019 Qtr 4 December 769.63 769.63 4254.08 2020 Qtr 1 January 743.95 743.95 4998.03 2020 Qtr 1 February 728.58 728.58 5726.60 2020 Qtr 1 March 832.41 832.41 6559.01 2020 Qtr 2 April 802.21 802.21 7361.22 2020 Qtr 2 May 459.49 459.49 7820.72 2020 Qtr 2 June 0.00 7820.72 7820.72

Can we make it accummulate on each row like shown on the column above?

Thank you1

Resolver I

Please create a measure like this and try:

``````CumulativeSum =
CALCULATE(
[Measures)],
FILTER(
ALLSELECTED('YourTable'[Month]),
ISONORAFTER('YourTable' [Month], MAX('YourTable' [Month]), DESC)
)
)``````
Helper I

Hi @vin26 ,

Thank you for your suggestion!

I tried to use your formula, it yielded just the same like my original formula:

Below is the code:

``````Measure Vin26 = CALCULATE([Measures],
FILTER(
ALLSELECTED(Consolidated[Date].[Month]),
ISONORAFTER(Consolidated[Date].[Month], MAX(Consolidated[Date].[Month]), DESC)
)
)``````

Something wrong with my formula?

Thank you!

Resolver I

@Anonymous as a quick solution please create a new column for month

Super User

Try my given formula and you'll get your expected answer:

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Super User

Again posting for your reference:

Cum_SUM = CALCULATE([Measures],FILTER(ALL(Table),'Table'[Date]<=MAX(Table'[Date])))
Dont forget to give THUMBS UP.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Helper I

Hi @Tahreem24 ,

Thank you for your suggestion! Sorry just got to try your formula. In my PBI it did not add up unlike yours.

How did you make it work? I think I copied your formula template just right:

``MEASURE TAHREEM = CALCULATE(([Measures]),FILTER(ALL(Consolidated),Consolidated[Date]<=MAX(Consolidated[Date])))``

Thank you!

Super User

I have replicated your sample at my side and it works seemlessly fine at my side:

If possible so can you attached your excel or PBIX by masking any confidential data.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Helper I

Hi @Tahreem24 and everyone,

I think you can make it work because you made the "Measure" a column, not a measure.

Here's the link to the PBI with data model exactly matched with this case.

Thank you!

Resolver I

@akbjf  First remove date hierarchy

then create a measure:

``````Measure2 = SUMX (
FILTER (
ALLSELECTED ( data1[Date] ),
data1[Date] <= MAX ( data1[Date] )
),
[Measure]
)``````

Super User

TRy the below measure:

Measure 2 = SUMX(FILTER(ALL(data1[Date]),data1[Date]<=MAX(data1[Date])),[Measure])

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
Helper I

Hi @Tahreem24 ,

Thank you for your support!

I accept it for solution.

Community Support

Hi @akbjf ,

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Hi Zhi,

Thanks for the solution, it works perfectly!

Anyway, is there a way so that the cumulative is reset to 0 when it reaches new fiscal year?

Fiscal year --> start July 1st - June 30th

Thank you!

## Helpful resources

Announcements

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors