Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm hoping someone can assist. I have a table of document reference numbers, datestamps of when they were validated ("Hits"), and who validated them, as part of a support process:
DocId | Hit User | Hit Date | Hit User Department |
400029 | Jimmy James | 23/09/2019 10:43 | Sales |
400029 | Jimmy James | 21/08/2019 10:43 | Sales |
400029 | Kelly kellyson | 23/09/2019 10:43 | Finance |
400029 | Kelly kellyson | 23/09/2019 10:43 | Finance |
400029 | Peter Peterson | 4/08/2019 12:05 | Services |
400030 | Peter Peterson | 4/08/2019 12:05 | Services |
400030 | Petra Petrason | 23/09/2019 10:43 | C-Suite |
400030 | Sarah Sarahson | 4/08/2019 12:05 | Finance |
400030 | Sarah Sarahson | 21/08/2019 10:43 | Finance |
400030 | Jimmy James | 23/09/2019 10:43 | Sales |
400030 | Kelly kellyson | 4/08/2019 12:05 | Finance |
400030 | Kenny Kennyson | 23/09/2019 10:43 | Sales |
400030 | Kenny Kennyson | 21/08/2019 10:43 | Sales |
400030 | Mark Markson | 4/08/2019 12:05 | Sales |
400030 | Mark Markson | 23/09/2019 10:43 | Sales |
400035 | Michael Hutchens | 4/08/2019 12:05 | Services |
400035 | Peter Peterson | 23/09/2019 10:43 | Services |
400035 | Petra Petrason | 4/08/2019 12:05 | C-Suite |
400035 | Petra Petrason | 21/08/2019 10:43 | C-Suite |
400035 | Petra Petrason | 4/08/2019 12:05 | C-Suite |
400035 | Petra Petrason | 23/09/2019 10:43 | C-Suite |
400035 | Sarah Sarahson | 21/08/2019 10:43 | Finance |
I'd like to be able to create a table that shows the following:
DocID | Total Hits Last Month | % Change Since Month -2 | % Usage Last Month | Position Change From Month -2 |
400029 | 3 | +150% | 33% | +1 |
400030 | 4 | -66% | 44% | -- |
400035 | 2 | -40% | 22% | -1 |
Rules
Any help would be greatly appreciated!
Solved! Go to Solution.
Hi mhutchens81,
You could refer to my sample file to see whether it work or not.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dax , that looks to be 95% of what I need - I really appreciate your quick response!
Would it be possible to avoid hard-coding month names and numbers into the calculations? That way I can use this as a rolling monthly report as it would refer to 'last month' and 'the month before last month', rather than 'August' or 'month 8' (for example) specifically:
Hi
You could use below expression to replace september(9)
MONTH(TODAY())-1
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
One last thing sorry - I've modified the two measures below, but I'm getting 'infinity' results. Any idea what I'm doing wrong?
Hi mhutchens81,
There is no monthno in your expressoion. If your [Hit Date] is date type, you could use monthno function in expression
% Usage Last Month = [sep count]/CALCULATE(COUNT(t3[DocId]), FILTER(ALL(t3), t3[Hit Date].[MonthNo]=MONTH(TODAY())-1))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |