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

[Martine10]

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	New value
1	A	1	2018	50	50
1	A	1	2019	20	20
1	A	1	2020	-10	0
1	A	1	2021	20	0

PS: It concerns a table with about 840.000 rows. (Groups (3) x categories (20) x countries (100) x years  (140). In terms of performance, it it best to do this in Power Query or in DAX? If I use a DAX measure I need to make sure the transformation is done on the group level so not aggregate countries and categories etc. 

[smpa01]

@Martine10  are you getting it from a SQL server? If there is no filter context involved for this task, doing the transformation server side will give you the optimimum performnace.

For example, in TSQL

declare @t1 as table (grp int,cat varchar(10), country int, year int, value int, row int) 
insert into @t1 
select 
  * 
from 
  (
    values 
      (1, 'A', 1, 2018, 50, 1), 
      (1, 'A', 1, 2019, 20, 2), 
      (1, 'A', 1, 2020,-10, 3), 
      (1, 'A', 1, 2021, 20, 4), 
      (1, 'B', 1, 2018, 50, 5), 
      (1, 'B', 1, 2019,-20, 6), 
      (1, 'B', 1, 2020,-10, 7), 
      (1, 'B', 1, 2021, 20, 8)
  ) t(a, b, c, d, e, f);
with cte1 as(
  select 
    grp, cat, country, year, value, row, 
    CASE when sum(value) over ( partition by grp, cat, country, year )< 0 then 0 else null end as _helper 
  from  @t1
) 
select 
  grp, cat, country, year, value, row, 
  CASE WHEN max(_helper) over(partition by grp, cat, country order by year rows unbounded preceding ) IS NULL then value 
      else max(_helper) over (partition by grp, cat, country order by year rows unbounded preceding ) end as Desired 
from cte1 
order by row

[CNENFRNL]

Aside from calculated column with DAX, Table.MinN() in PQ easily does the trick in your scenario. Since the large dataset is logically sliced into many small ones, I expect the performance is satisfactory.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJDsAgCAXQu7DGBBw6LHsOw/2vUaBNuuh3Yb74VBLmJCWmy1dkFT08hpDxT84MIH7IVBSSfo/qK+P5TvISAF1BlB1By/5Iem4BRJSGZPOAsC+7xMwqmd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Category = _t, Country = _t, Year = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Category", "Country"}, {{"ar", each let tb = Table.MinN(_, "Year", each [Value] > 0), tb0 = Table.ReplaceValue(Table.Skip(_, Table.RowCount(tb)), each [Value], 0, Replacer.ReplaceValue, {"Value"}) in tb & tb0}}),
    #"Combined Tables" = Table.Combine(#"Grouped Rows"[ar])
in
    #"Combined Tables"

[VahidDM]

Hi @Martine10 

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/