Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bog12345
Frequent Visitor

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

 

without chaning.PNG

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: 

with chaning.PNG

 

 

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.

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

 

Hope it can help you!Smiley Tongue

 

Best regards,

Cherry

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

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

 

Hope it can help you!Smiley Tongue

 

Best regards,

Cherry

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

Thanks, it works 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.