Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX help

Hi, I have data as mentioned below and wanted to create New Val calculated column, whenever there is negative make it zero and next row record should be substracted negative values.   In the below...
Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    1 year ago

    Hello Anonymous ,
    I can suggest quickly with the help of creating INDEX in Power Query. 

    DAX for the new column will be like below:

    New Value = 
VAR PV = LOOKUPVALUE('Table'[Val],'Table'[Index],'Table'[Index]-1)
RETURN
IF('Table'[Val]<0,0,
IF('Table'[Index] =1,'Table'[Val],
IF('Table'[Val]>0 && PV < 0, 'Table'[Val] + PV , 'Table'[Val])))

    Output looks as below:

    Attached pbix as well for your refrence. 

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Kishore_KVN's avatar
Kishore_KVN
Solution Sage
1 year ago

Hello Anonymous ,
I can suggest quickly with the help of creating INDEX in Power Query. 

DAX for the new column will be like below:

New Value = 
VAR PV = LOOKUPVALUE('Table'[Val],'Table'[Index],'Table'[Index]-1)
RETURN
IF('Table'[Val]<0,0,
IF('Table'[Index] =1,'Table'[Val],
IF('Table'[Val]>0 && PV < 0, 'Table'[Val] + PV , 'Table'[Val])))

Output looks as below:

Attached pbix as well for your refrence. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Unfortunately I can't create Index, wanted to check using calculated column only.

    • Kishore_KVN's avatar
      Kishore_KVN
      Solution Sage
      1 year ago

      No, that not right. You can create index without which how you know the order of the value column?

      So, you have to figure out a way of creating index for your data. Atleast ROW NUMBER should be created.