Tim intelligence: % Change, Overall Usage %, & Position Change

[Anonymous]

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

• There is no particular order that the table rows are in - they could be all mixed up, or sorted by one or another column;
• In this example I'm using September 2019 as 'Last Month' and August 2019 as 'Month -2';
• % Usage Last Month = hits for the Doc last month, as a % of total hits for all articles in the top 20 (also from last month);
• Position Change From Month -2 = Ranking change for each DocID in the top 20. If no ranking change then it should be "--", and if the DocID is new to the top 20, then it should state "New";
• There is no particular order that the table rows are in - they could be all mixed up, or sorted by one or another column;
• In this example I'm using September 2019 as 'Last Month' and August 2019 as 'Month -2';
• % Usage Last Month = hits for the Doc last month, as a % of total hits for all articles in the top 20;
• Position Change From Month -2 = Ranking change for each DocID in the top 20. If no ranking change then it should be "--", and if the DocID is new to the top 20, then it should state "New".

Any help would be greatly appreciated!

[dax · Accepted 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.

[Anonymous]

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:

[dax]

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.

[Anonymous]

One last thing sorry - I've modified the two measures below, but I'm getting 'infinity' results. Any idea what I'm doing wrong?

% Usage Last Month = [HITS_LAST_MONTH]/CALCULATE(COUNT(t3[DocId]), FILTER(ALL(t3), t3[Hit Date] = MONTH(TODAY())-1))

% Change Since Month -2 = if(([HITS_LAST_MONTH]-[LAST_MONTH-2])<0,"-", "+") & (1+([HITS_LAST_MONTH]-[LAST_MONTH-2])/CALCULATE(COUNT(t3[DocId]),FILTER(t3,t3[Hit Date] = MONTH(TODAY())-2)))*100 & "%"

[dax]

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.

[Anonymous]

Perfect, @dax ! You are a star 🙂 Thank you so much.