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

@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])))

amitchandak
Super User
Super User

@mhendel ,

Try like

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

@amitchandak 

 

Thanks for your help,

 

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

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.