Forum Discussion

bog12345's avatar
bog12345
Frequent Visitor
8 years ago
Solved

changed or not compared to the previous date

Hi,

 

I need help with Dax. 

There are 4 column in my test table as follows:

-ID: every customer have unique ID, every row means one subscription

-start of service: The start of the subscription

-end of service: the end of the subscription

-number of service: it has 3 possible value: 1 if the customer have 1 channel, 2 if 2 channel and 3 if 3 channel

 

I would like to create a calculate column, which indicate that the customer changed to a smaller subsciption or not (it means change to a fewer channel). I would like to see 0 if not and 1 if yes, so I would like to get the following table with Changing column: 

 

 

So the customer 1 (in the 4th row) get a 1 value, because he changed his previous subscription from 1 to 2.

 

Any help would be greatly appreciated.

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi bog12345,

     

    You could create the calculated column with the formula below.

     

    Chaging = 
    VAR previous_date =
        CALCULATE (
            MAX ( 'Table'[Start of service] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[Start of service] < EARLIER ( 'Table'[Start of service] )
            )
        )
    VAR previous_Number =
        LOOKUPVALUE (
            'Table'[Number of Service],
            'Table'[ID], 'Table'[ID],
            'Table'[Start of service], previous_date
        )
    RETURN
        IF (
            previous_Number = BLANK ()&&'Table'[ID]='Table'[ID],
            0,
            IF ( previous_Number = 'Table'[Number of Service]&&'Table'[ID]='Table'[ID], 0, 1 )
        )

    Then you will get the output you expected.

     

     

    Hope it can help you!:smileytongue:

     

    Best regards，

    Cherry

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi bog12345,

     

    You could create the calculated column with the formula below.

     

    Chaging = 
    VAR previous_date =
        CALCULATE (
            MAX ( 'Table'[Start of service] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[Start of service] < EARLIER ( 'Table'[Start of service] )
            )
        )
    VAR previous_Number =
        LOOKUPVALUE (
            'Table'[Number of Service],
            'Table'[ID], 'Table'[ID],
            'Table'[Start of service], previous_date
        )
    RETURN
        IF (
            previous_Number = BLANK ()&&'Table'[ID]='Table'[ID],
            0,
            IF ( previous_Number = 'Table'[Number of Service]&&'Table'[ID]='Table'[ID], 0, 1 )
        )

    Then you will get the output you expected.

     

     

    Hope it can help you!:smileytongue:

     

    Best regards，

    Cherry