dax help

[sarath_chandra]

HI , in my dataset ,im having setup columns S1,S2,S3,S4,...upto S12.and date column - first day of every month (1/1/2018 to 12/1/2022) and field values . I need previous row difference of each rows .but first row of each setup should be null or zero (it should not take difference of previous set ups) for example s2 should not take the field values of s1. so that particular row should be zero or null. and in difference i have to use ABS dax function to avoid negative sign in output column.

kindly see the data set.please help me out. vry thankfull for your help

[Anonymous · Accepted solution]

Hi @sarath_chandra ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR cur_val =
    SELECTEDVALUE ( 'Table'[Field Value] )
VAR cur_st =
    SELECTEDVALUE ( 'Table'[Setup] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Setup] = cur_st && 'Table'[Date] < cur_date )
VAR pre_date =
    MAXX ( tmp, [Date] )
VAR pre_val =
    CALCULATE (
        MAX ( 'Table'[Field Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = pre_date && 'Table'[Setup] = cur_st )
    )
RETURN
    IF ( DAY ( cur_date ) = 1, 0, ABS ( cur_val - pre_val ) )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Hi,

Starting from this

You obtain your result

applaying the steps you find in the attached file

If this post is useful to help you to solve your issue consider giving the post a thumbs up  and accepting it as a solution !