Help creating calculated columns with DAX!!

[Anonymous]

Hello PowerBI community! 

New Business Window Explained:

New Business is defined as customers that have not done business with us for 5 years.

If a customer hasn't had a booking in the last 5 years (including new customers that have not done any bookings in the past), any bookings for that customer are considered New Business for 12 months (365 days). 

Bookings Table (each entry is a booking):

Customer Name	isCancelled	Booking Date
Business 1	FALSE	3/25/2022
Business 2	FALSE	1/20/2016
Business 2	FALSE	8/2/2022
Business 3	FALSE	7/4/2015

Desired Outcome: 

Customer Name	Latest Booking Date	Days Remaining	Business Window Expiry
Business 1	3/25/2022	236	3/25/2023
Business 2	8/2/2022	365	8/2/2023
Business 3	7/4/2015	0	7/4/2016

Explanation: The outcome is a table with unique values for the "Customer Name" column.

For Business 1, the latest and only booking date was 3/25/2022. The window is 365, so the Expiry is on 3/25/2023. From today (8/2/22 at the time of writing) to 3/25/2023, there are 236 days, which is the value in Days Remaining. Business 1 is eligible for a new business window because there are no prior transactions (is a new customer). 

For Business 2, the latest booking date was 8/2/2022 and the booking before that one happened well over 5 years prior (1/20/2016). Because 5 years (1825 days) have passed, the latest booking on 8/2/2022 initiates a "New Business Window", which expires on 8/2/2023 and is 365 days away from today (8/2/2022 at the time of writing). 

For Business 3, the "New Business Window" happened from 7/4/15 to 7/4/16, but that was in the past and there are no days remaining. 

I know this is a lot, but I really need help. 

[Anonymous · Accepted solution]

Hi @Anonymous,

You can use the following formulas to get the remaining days based on the current date and expiry date:

Expired Date = 
IF (
    'Table'[Booking Date]
        = MAXX (
            FILTER ( 'Table', [Customer Name] = EARLIER ( 'Table'[Customer Name] ) ),
            [Booking Date]
        ),
    DATE ( YEAR ( [Booking Date] ) + 1, MONTH ( [Booking Date] ), DAY ( [Booking Date] ) )
)

Days Remaining =
IF (
    [Expired Date] <> BLANK (),
    IF ( [Expired Date] >= TODAY (), DATEDIFF ( TODAY (), [Expired Date], DAY ), 0 )
)

Regards,

Xiaoxin Sheng