The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I have table such as below:
Start Date | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Product ID |
1/1/20 | 1/3/20 | 1/3/20 | 1 | |||
5/5/20 | 9/9/23 | 5/20/24 | 2 | |||
8/8/20 | 1/1/24 | 2/1/24 | 3 | |||
11/11/20 | 5/20/24 | 6/1/24 | 6/1/24 | 4 | ||
5/6/20 | 5/6/24 | 1/10/25 | 2/1/25 | 5 |
with many more rows.
Does anyone know how to flag a row where the dates in columns Date 1 - Date 5 (disregarding Start Date) are within 60 days of each other (disregarding duplicate dates)?
Goal:
Start Date | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Product ID | Flag |
1/1/20 | 1/3/20 | 1/3/20 | 1 | no | |||
5/5/20 | 9/9/23 | 5/20/24 | 2 | no | |||
8/8/20 | 1/1/24 | 2/1/24 | 3 | yes | |||
11/11/20 | 5/20/24 | 6/1/24 | 6/1/24 | 4 | yes | ||
5/6/20 | 5/6/24 | 1/10/25 | 2/1/25 | 5 | yes |
Thank you so much!
Solved! Go to Solution.
it's better to do the data transform. pls see the workaround below
Proud to be a Super User!
why we flag no for the first row? is that because the dates are duplicated?
maybe you can try this
select startdate and product id columns, then unpivot other columns
then create a column
Proud to be a Super User!
I am trying to use your new column as inspiration and came up with the following. However, everything is yes, perhaps due to blanks? Would you have any idea on how to improve it please?
Hi ryan_mayu, thank you for your response! I do have a few follow ups please:
1) Yes, first row is not flaged due to duplicate; please disregard duplicates.
2) Sadly Min and Max will not work for all rows in data. I added Product ID 5 just now where there are 3 different dates across Date 1 - Date 5 columns and the middle date is wihtin 60 days of max date. I'm not sure if in the future we may have 5 different dates in all 5 columns.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
142 | |
109 | |
107 | |
74 | |
61 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |