cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhendel
Helper III
Helper III

Cumulative sum with condition

Hello

 

This is my situation : 

 

DateAmountConditionCumulative if Condition = Y
01-01-180 0
01-02-180 0
01-03-180 0
01-04-180 0
01-05-180 0
01-06-180 0
01-07-180 0
01-08-180 0
01-09-180 0
01-10-1857896Y57896
01-11-180 57896
01-12-180 57896
01-01-190 57896
01-02-190 57896
01-03-190 57896
01-04-190 57896
01-05-190 57896
01-06-19115000Y172896
01-07-190 172896
01-08-1925000N172896
01-09-190 172896
01-10-190 172896
01-11-1938125Y211021
01-12-1956000N211021
01-01-20  211021

 

The 3 first columns are the data in.  The last column is the result i'd like to have : a cumulative sum based on date, with condition set to Y.  I tried to build a column with a CALCULATE function but i missed something...

 

Could you help me?

 

Thanks in advance and sorry for my poor English.

 

Michael Hendeles

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to create a calculated column first:

New Amount = IF('Table'[Condition]="N",0,'Table'[Amount])

Then try this measure:

Measure = CALCULATE(SUM('Table'[New Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))

The result shows:

8.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

10 REPLIES 10
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to create a calculated column first:

New Amount = IF('Table'[Condition]="N",0,'Table'[Amount])

Then try this measure:

Measure = CALCULATE(SUM('Table'[New Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))

The result shows:

8.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

@v-gizhi-msft  : thanks! You make my day!

camargos88
Community Champion
Community Champion

Hi @mhendel ,

 

Try creating this measure:

 

Running Total = CALCULATE(SUM('Table'[Amount]); FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX('Table'[Date])); 'Table'[Condition] = "Y")
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88  : i forgot the @...

Thanks for your help

 

Here is the result with your measure : 

DateRunning Total
01-10-18 00:0057896
01-11-18 00:0057896
01-12-18 00:0057896
01-01-19 00:0057896
01-02-19 00:0057896
01-03-19 00:0057896
01-04-19 00:0057896
01-05-19 00:0057896
01-06-19 00:00172896
01-07-19 00:00172896
01-08-19 00:00172896
01-09-19 00:00172896
01-10-19 00:00172896
01-11-19 00:00211021
01-12-19 00:00211021
01-01-20 00:00211021

 

Unfortunately, the rows with an amount of 0 are missing...

 

In fact i had the same result...

@mhendel ,

Try like

 

Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(date,date[date] <=max(Table[ Date])))

 

Or

Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(All(Table),Table[date] <=max(Table[ Date])))
//old
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(all(Table),Table[date] <=max(Table[ Date])),Table[Condition]="Y")

 

 

Also, try the option with old Formula

ShowItemwithoutdata.JPG

 

What error you are getting



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak 

 

Capture.PNG

 

after table[Amount] it will be  ; not ,

Cumm Sales = CALCULATE(SUMX(Table;if(Table[Condition]="Y";Table[Amount];0));filter(All(Table);Table[date] <=max(Table[ Date])))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
amitchandak
Super User
Super User

@mhendel ,

Try like

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=max(Table[ Date])),Table[Condition]="Y")



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak 

 

Thanks for your help,

 

When i try your measure, i receive an error... Are you sure with your syntax?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

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

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors