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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply

Cumulative column after name filter

Hello,

I would like to create a column that calculates cumulative values after filtering the ProjectName column.

 

 NonCumulativeValues.png

 

So for example, if I filter the ProjectName column and choose PRO_01_OnTraget, then the result should be like that:

 

CumulativeValues.png 

 

I would appreciate your assistance so much.

 

Best regards,

Hayman

1 ACCEPTED SOLUTION

I found how it should be:

Since the filter is based on a Text value (not date nor numerical), then filter using the "Values" function

So it should be:

 

=CALCULATE(SUM('Table1'[NonCumulativeValues] ), FILTER ( ALL ( 'Table1' ), [StartPeriod] <= MAX ( [StartPeriod] )), VALUES ( 'Table1'[ProjectName] ) ) ) 

 

Best regards,

Hayman

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

What determines the order?  Do you have date column that can be used?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dear @Phil_Seamark

I can have a date column that will look like that

 

NonCumulativeValues with Date.png

 

Regards,

Hayman

Give this measure a crack.  I think it might be close

 

Measure =
CALCULATE (
    SUM ( 'Table1'[NonCumulativeValues] ),
    FILTER (
        ALL ( 'Table1' ),
        [StartPeriod] <= MAX ( [StartPeriod] )
            && Table1[ProjectName] = MAX ( 'Table1'[ProjectName] )
    )
)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

I get an error:

The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String

 

I think this is coming from the last MAX function in the measure, since the "ProjectName" is a string.

 

Please advise,

Regards,

Hayman

I found how it should be:

Since the filter is based on a Text value (not date nor numerical), then filter using the "Values" function

So it should be:

 

=CALCULATE(SUM('Table1'[NonCumulativeValues] ), FILTER ( ALL ( 'Table1' ), [StartPeriod] <= MAX ( [StartPeriod] )), VALUES ( 'Table1'[ProjectName] ) ) ) 

 

Best regards,

Hayman

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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