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

help me plzzzz 

[tamerj1]

Hi @sarath_chandra 

please try

Difference =
VAR CurrentDate = TableName[Date]
VAR CurrentValue = TableName[Value]
VAR CurrentSetupTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Setup] ) )
VAR PreviousRecords =
FILTER ( CurrentSetupTable, TableName[Date] < CurrentDate )
VAR PreviousRecord =
TOPN ( 1, PreviousRecords, TableName[Date] )
VAR PreviousValue =
COALESCE ( MAXX ( PreviousRecord, TableName[Value] ), CurrentDate )
RETURN
ABS ( CurrentDate - PreviousValue )

[FreemanZ · Accepted solution]

hi @sarath_chandra 

try to add a column with this:

Output2 =

VAR _setup = TableName[SETUP]

VAR _date = TableName[Date]

VAR _value =

MINX(

    FILTER(

        TableName,

        TableName[SETUP]= _setup

            &&VALUE(FORMAT(TableName[Date], "YYYYMM")) = VALUE(FORMAT(TableName[Date], "YYYYMM"))-1

    ),

TableName[Value]

)

RETURN

IF(TableName[Date]<>DATE(2018,1,1), ABS(TableName[OUTPUT] - _value),0)

I tried and it worked like this:

[sarath_chandra]

@FreemanZ @amitchandak @tamerj1 plz help me out from this

[sarath_chandra]

plz help @Anonymous @v-jianboli-msft .very thank ful