cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akbjf
Helper I
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:
snip3.PNG
 
Now, I visualized using table, but it's not accummulated. 
 
snip4.PNG
What I want is like this, accummulated:
YearQuarterMonthMeasures
2019Qtr 3July857.46
2019Qtr 3August1502.83
2019Qtr 3September2143.54
2019Qtr 4October2819.25
2019Qtr 4November3484.40
2019Qtr 4December4254.06

 

Can you help me on this?

 

THank you!

3 ACCEPTED SOLUTIONS
dax
Community Support
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.

View solution in original post

@akbjf  First remove date hierarchy

PBI_4.JPG 

 

then create a measure:

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

 

PBI_3.JPG 

View solution in original post

@akbjf ,

TRy the below measure:

Measure 2 = SUMX(FILTER(ALL(data1[Date]),data1[Date]<=MAX(data1[Date])),[Measure])
Capture.PNG
 
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

View solution in original post

18 REPLIES 18
amitchandak
Super User
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 !!
vin26
Resolver I
Resolver I

Try this:

 

CumulativeSum =

CALCULATE(

    [Measures)],

    FILTER(

        ALLSELECTED('YourTable'[Month]),

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

    )

)

Tahreem24
Super User
Super User

@akbjf ,

 

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
Mariusz
Community Champion
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

 

Hi Mariusz!

 

THank you for your suggestion.

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

YearQuarterMonthMeasuresMeasure MariuszShould be like this
2019Qtr 3July857.5857.50857.50
2019Qtr 3August645.4645.401502.90
2019Qtr 3September640.71640.712143.62
2019Qtr 4October675.69675.692819.31
2019Qtr 4November665.13665.133484.44
2019Qtr 4December769.63769.634254.08
2020Qtr 1January743.95743.954998.03
2020Qtr 1February728.58728.585726.60
2020Qtr 1March832.41832.416559.01
2020Qtr 2April802.21802.217361.22
2020Qtr 2May459.49459.497820.72
2020Qtr 2June 0.007820.72
    7820.72 

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

 

Thank you1

Please create a measure like this and try:

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

Hi @vin26 ,

 

Thank you for your suggestion!

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

snip5.PNG

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!

 

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

 

PBI_1.JPG

 

PBI_2.JPG

 

 

@akbjf ,

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

Capture.PNG

 

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

@akbjf , 

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

Hi @Tahreem24 ,

 

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

snip6.PNG

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!

@akbjf ,

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.

Capture.PNG

 

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

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.

 

Link to PBIX 

 

Thank you!snip7.PNG

@akbjf  First remove date hierarchy

PBI_4.JPG 

 

then create a measure:

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

 

PBI_3.JPG 

@akbjf ,

TRy the below measure:

Measure 2 = SUMX(FILTER(ALL(data1[Date]),data1[Date]<=MAX(data1[Date])),[Measure])
Capture.PNG
 
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

Hi @Tahreem24 ,

 

Thank you for your support!

I accept it for solution.

dax
Community Support
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.

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors