Forum Discussion
Checking if value exist for 45 days
Hi,
I want to check that if a value exist for 45 daysbefore a spesific date. In this way i want to find that if that order is the first order after 45 days for every user.
Here is my example table. I want to create the check column.
| customer id | order date | check |
| 1 | 10.10.2022 | no (first order) |
| 1 | 12.10.2022 | yes (it's just 2 days) |
| 1 | 12.02.2023 | yes (first order after 4 months) |
| 2 | 01.09.2022 | no (first order) |
| 2 | 30.10.2022 | yes (fisrt order after several days) |
| 2 | 02.11.2022 | no |
Thank you very much
Hi selinozdemirpbi ,
According to your description, you want to get the first day more than 45 days away from the first order, here's my solution. Add a custom column in Power Query:
let min=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[customer id]=[customer id])[order date]) in if [order date]=List.Min(Table.SelectRows(#"Changed Type",each Duration.Days([order date]-min)>=45)[order date]) then "yes" else "no"Get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
What is your logic for yes and no...Your last order for ID 1 is after 4 months but marked as yes.
Chk = VAR __interval = [order date] - CALCULATE( MIN( 'Order'[order date] ), ALLEXCEPT( 'Order', 'Order'[customer id] ) ) RETURN SWITCH( TRUE(), __interval = 0, "First", __interval < 45, "Several days", "No" )Hi selinozdemirpbi ,
According to your description, you want to get the first day more than 45 days away from the first order, here's my solution. Add a custom column in Power Query:
let min=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[customer id]=[customer id])[order date]) in if [order date]=List.Min(Table.SelectRows(#"Changed Type",each Duration.Days([order date]-min)>=45)[order date]) then "yes" else "no"Get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
