Forum Discussion
Dax issue - Multiple conditions on date data
Hello All,
I need help with my dax.
In my data set, I have:
Type - New, return, or seasonal customers
Subscription level - Gold, Silver, Bronze, Diamond
Sub - Start and End date.
Goal: I'd like to calculate how many days my customer went offline before resubscribing under certain conditions.
Scenario - if my customer type is new or return and customer sub level is gold or bronze then calculate the end date minus the start date of my customers next purchase if the customer type is seasonal.
Answer = 150 days
P.s - condition - Everytime a new or returning customer subs with level gold or bronze the system automatically assigns their next type to seasonal.
| Invoice no | Customer ID | Type | Sub level | Start date | End date |
| 101 | 500 | New | Gold | 8/1/2022 | 8/30/2022 |
| 102 | 601 | Return | Diamond | 10/1/2022 | 10/1/2023 |
| 103 | 500 | Seasonal | Diamond | 03/02/2023 | 03/30/2023 |
| 104 | 606 | Return | Diamond | 7/31/2023 | 8/2/2023 |
| 105 | 700 | Retun | Bronze | 06/1/2023 | 06/30/2023 |
| 106 | 700 | Seasonal | Bronze | 11/1/2023 | 11/26/2023 |
| 107 | 701 | New | Diamond | 04/2023 | 05/2023 |
For the table above the dax should calculate:
Customer 500: 8/30/2022 - 03/02/2023 = 184 days
Customer 700: 6/30/2023 - 11/1/2023 = 124 days
Thank you!
D
3 Replies
pls try this
Ahmedx, thank you.
The dax worked using the sample data set however when tested in my live data, it didn't work as expected.
In real world situation, customer 500 may have multiple transactions up to 5 or 6; hence the the dax has to tie every gold or bronze to its next seasonal sub.
P.s - condition - Everytime a new or returning customer subs with level gold or bronze the system automatically assigns their next type to seasonal.Invoice no Customer ID Type Sub level Start date End date 99 500 New Silver 09/1/2020 11/1/2022 100 700 New Silver 11/2/201 11/20/2021 101 500 New Gold 8/1/2022 8/30/2022 103 500 Seasonal Diamond 03/02/2023 03/30/2023 P.S - For every seasonal customer Type, the previous sub level must be gold or bronze.
