Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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.

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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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 , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.