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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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