Forum Discussion
dax help
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
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.
2 Replies
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 !
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.
