Forum Discussion

surajbh's avatar
surajbh
Helper 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) and primary key, sales )

 

Let me know if the above question/query is still unclear. 
Thank you very much for the support. 

Greeting,

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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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
        Community 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.

         

  • surajbh's avatar
    surajbh
    Helper I
    4 years ago

    v-yingjl  thank you for the support. 
    if in the above-given scenario, the user has the possibility of filtering or choosing ( two dates- one to another date ), that will determine the status of the primary key. 
    How could you write DAX.
    thank you in advance for the support.