Forum Discussion

sarath_chandra's avatar
sarath_chandra
Icon for Helper III rankHelper III
3 years ago
Solved

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 ....
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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 )