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
Surveillance Days =
SWITCH (
TRUE (),
'Customer '[Type] = "New" ||'Customer '[Type] = "Return"
&& 'Customer '[Subscription level] = "Gold" || 'Customer '[Subscription level] = "Bronze" ,
DATEDIFF ('Sub'[Start_Date], 'Sub'[End_Date], DAY),
'Customer '[Type]= "Seasonal",
DATEDIFF ('Sub'[Start_Date], 'Sub'[], DAY),
BLANK ()
)
Thank you!
D
