Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to use i.e. traffic lights to display the status of a customer. Basically I want to show a red light if the customer have not made any order for a longer period of time, compared to his buying pattern (history). For instance if a customer have ordered things usually every week for a long time, and it is now two weeks since his last order, I want to show a red light.
I don't know how I should approach this, especially how to determine the customers buying pattern, and then how to compare that to the last period....
Anyone that have solved a similar problem?
Any help would be deeply appreciated.
Solved! Go to Solution.
I would create a measure that calculates the AVERAGE for a customer. Perhaps like:
Measure 7 = VAR __customer = MAX('Table5'[Customer]) VAR __table = FILTER('Table5',[Customer] = __customer) VAR __table1 = ADDCOLUMNS(__table,"__days",DATEDIFF(MAXX(FILTER(__table,[Date] < EARLIER([Date])),[Date]),[Date],DAY)) RETURN AVERAGEX(__table1,[__days])
I would create a measure that calculates the AVERAGE for a customer. Perhaps like:
Measure 7 = VAR __customer = MAX('Table5'[Customer]) VAR __table = FILTER('Table5',[Customer] = __customer) VAR __table1 = ADDCOLUMNS(__table,"__days",DATEDIFF(MAXX(FILTER(__table,[Date] < EARLIER([Date])),[Date]),[Date],DAY)) RETURN AVERAGEX(__table1,[__days])
Thanks for your answer Greg, much appreciated. I have tried to apply your answer to my data but my knowledge about measures is very limited. Based on your example I tried the following on my specific data:
I finally got it to work, and as I said, it was my lack of understanding of measures. Once I got a hang of that, it was pretty straight forward. Thanks Greg.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |