Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

For each group find first negative value and replace subsequent years with zero

Hello, how can I get the first negative number for each Group, Category and Country and then make the new value for that year and years after value zero?   Group Category Country Year Value ...
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Anonymous 

 

Try this code to add a new column to your table:

New value =
VAR _CY = [Year]
VAR _LY = _CY - 1
RETURN
    IF(
        [Value] < 0,
        0,
        IF(
            CALCULATE(
                MAX( 'Table'[Value] ),
                FILTER(
                    ALL( 'Table' ),
                    'Table'[Group] = EARLIER( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER( 'Table'[Category] )
                        && 'Table'[Country] = EARLIER( 'Table'[Country] )
                        && 'Table'[Year] = _LY
                )
            ) < 0,
            0,
            [Value]
        )
    )

 

Output:

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/