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!!

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Anonymous , Try using below dax for calculated column

 

New Val =
VAR CurrentVal = 'Table'[Val]
VAR PreviousRow =
CALCULATE(
SUM('Table'[Val]),
FILTER(
'Table',
'Table'[Dt] = EARLIER('Table'[Dt]) &&
'Table'[role] = EARLIER('Table'[role]) - 1
)
)
VAR Adjustment =
IF(PreviousRow < 0, -PreviousRow, 0)
RETURN
IF(CurrentVal < 0, 0, CurrentVal - Adjustment)

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the response, but previous val is not picking up and how will it take if any previous day has negative values. it should check current and previous date along with role as well.