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

  • 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:

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    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 )