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.
Hi, hopefully someone can help with this? I have a table that looks like the below
Client_ID | Year | Month | Tickets_Created |
1044 | 2019 | Jul | 92 |
1044 | 2019 | Aug | 65 |
1044 | 2019 | Sep | 33 |
1044 | 2019 | Oct | 12 |
1044 | 2019 | Nov | 10 |
1044 | 2019 | Dec | 2 |
What I want to do is analyze Client_IDs who have a consecutive Tickets_Created on a month by month basis. To do this I created a calculated column which compared the current month to the previous month
Client_ID | Year | Month | Tickets_Created | Difference |
1044 | 2019 | Jul | 92 | -2 |
1044 | 2019 | Aug | 65 | -27 |
1044 | 2019 | Sep | 33 | -32 |
1044 | 2019 | Oct | 12 | -20 |
1044 | 2019 | Nov | 10 | -2 |
1044 | 2019 | Dec | 2 | -8 |
Now what I want is using the above, get a table that shows the top ten Client_IDs that have the most consecutive months decrease in tickets. So for example, the above Client_ID would have a consecutive decrease of 6 months. If the same Client_ID had an increase this month (compared to November) then they would no longer show on the top 10 table as this would reset the conescutive months they have decreased.
I hope this makes sense? Appreciate any help
Hi, @Anonymous
I have spend some time together with experts using some random sample. We found that you can make the consecutive count, but the tricky part is you have to re-count each time when there is increase in the tickets.
I am afraid that DAX do not support this kind of calculation, maybe try with power query.
This is my consecutive count formula if needed:
Count concecutive decrease =
IF (
[Differece] < 0,
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Client_ID] ), [Differece] < 0 ),
'Table'[Month] <= EARLIER ( 'Table'[Month] )
) + 0,
BLANK ()
)
Best
Paul
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |