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
Regulate
Regular Visitor

Maximum date difference between consecutive purchases for a customer

Hi!

 

I am having troubles with calculating the maximum difference between two consecutive purchases for each customer. Note that this is not the same as date difference between first and last purchase.

 

I have two tables (Purchase_table and Customer_table) which are related via CustomerID. In the Purchase_table the same CustomerID's are existing multiple times. As shown in the example pictures, I would like to calculate values for the column Max_diff_betw_purch. In the example I manually inputted value "8" for customer "1001", since it is the date difference between his second and third purchase, which is bigger than the difference between his first and second purchase.

 

Purchase_table.png

Customer_Table.png

Any help with this? I would prefer to do this with Power BI Desktop in data mode by adding a new column (Max_diff_betw_purch).

 

 

Sincerely,

L

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@Regulate

 

You can use this column in the Customers Table

 

Max_diff_between_Purchase =
VAR temp =
    CALCULATETABLE ( VALUES ( Purchase_table[PurchaseDate] ) )
VAR temp1 =
    ADDCOLUMNS (
        temp,
        "Days", DATEDIFF (
            [PurchaseDate],
            MINX (
                FILTER ( temp, [PurchaseDate] > EARLIER ( [PurchaseDate] ) ),
                [PurchaseDate]
            ),
            DAY
        )
    )
RETURN
    MAXX ( temp1, [Days] )

Regards
Zubair

Please try my custom visuals

View solution in original post

@Regulate

 

Please see attached file with your sample data

 

maxdays.png


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@Regulate

 

You can use this column in the Customers Table

 

Max_diff_between_Purchase =
VAR temp =
    CALCULATETABLE ( VALUES ( Purchase_table[PurchaseDate] ) )
VAR temp1 =
    ADDCOLUMNS (
        temp,
        "Days", DATEDIFF (
            [PurchaseDate],
            MINX (
                FILTER ( temp, [PurchaseDate] > EARLIER ( [PurchaseDate] ) ),
                [PurchaseDate]
            ),
            DAY
        )
    )
RETURN
    MAXX ( temp1, [Days] )

Regards
Zubair

Please try my custom visuals

Perfect, thank you Zubair! Your column worked like a charm.

 

I really appreciate the help!

 

-L

@Regulate

 

Please see attached file with your sample data

 

maxdays.png


Regards
Zubair

Please try my custom visuals

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.