Forum Discussion

surajbh's avatar
surajbh
Icon for Helper I rankHelper I
4 years ago
Solved

Create a column with condition based on different columns

Hi all,  I want to create a formula in DAX, which create a case/ condition as column - status (New, updated,unchanged). that should compare between today vs yesterday (latest date as of yesterday) ...
  • surajbh's avatar
    surajbh
    4 years ago

    amitchandak  thank you for the code.
    if in the above scenario, if I want to find the delta of sales with reference today(the one which I filter) and yesterday (with reference to filtered)- what could you suggest to write in dax.
    thank you in advance. 

  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi surajbh ,

    The reference could be like this:

    Reference = 
VAR _last =
    MAXX (
        FILTER (
            'Table',
            [Date]
                = EARLIER ( 'Table'[Date] ) - 1
                && [Primary key] = EARLIER ( 'Table'[Primary key] )
        ),
        [Sales]
    )
return
[Sales] - _last

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

surajbh , Try a new column

 

new column =
var _last = maxx(filter(Table, [Date] =earlier([Date])-1 && [primary key] =earlier([primary key])),[sales])
return
Switch(true() ,
isblank(_last) , "New" ,
_last <> [sales] , "Updated"
,"same"
)

surajbh's avatar
surajbh
Icon for Helper I rankHelper I
4 years ago

amitchandak  thank you for the code.
if in the above scenario, if I want to find the delta of sales with reference today(the one which I filter) and yesterday (with reference to filtered)- what could you suggest to write in dax.
thank you in advance. 

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi surajbh ,

    The reference could be like this:

    Reference = 
VAR _last =
    MAXX (
        FILTER (
            'Table',
            [Date]
                = EARLIER ( 'Table'[Date] ) - 1
                && [Primary key] = EARLIER ( 'Table'[Primary key] )
        ),
        [Sales]
    )
return
[Sales] - _last

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.