Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
(Thank you in advance for help).
I have a table of Customers, the dates they inquire about our product, and the dates they actually purchase them. I then have a calculated column which tells me the Date Difference between the two.
I would like to know the best way to categorize these items into the following conversion breakdowns (column in purple font):
Within a week
Within a month
Within 3 months
Within 6 months
Within 1 year
Over a year
Grouping doesn't seem to be a good option because I don't have all of the values in my data set already.
Is a long IF Statement in another column the way to go, or is there something better?
Customer | InquiryDate | PurchaseDate | DaysBetween | Category |
ABCDEFG | 1/1/2020 | 2/3/2020 | 32 | Within 3 months |
HGIDLSJ | 5/1/2023 | 5/7/2023 | 6 | Within 1 week |
KOPNNOUHU | 3/1/2020 | 3/8/2021 | 372 | Over 1 Year |
JIOFIDOSHO | 9/1/2021 | 9/3/2021 | 2 | Within 1 week |
JNIJFKNI | 6/1/2022 | 6/27/2022 | 26 | Within 1 month |
Hi @apmulhearn ,
In this case believe the best option is the switch statement since the number of days can be variable, and you can define the less than X days to determine the 1 week, 1 months, and so on, you can do this also in Power query with the conditional column.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |