cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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:

 

DocIdHit UserHit DateHit User Department
400029Jimmy James23/09/2019 10:43Sales
400029Jimmy James21/08/2019 10:43Sales
400029Kelly kellyson23/09/2019 10:43Finance
400029Kelly kellyson23/09/2019 10:43Finance
400029Peter Peterson4/08/2019 12:05Services
400030Peter Peterson4/08/2019 12:05Services
400030Petra Petrason23/09/2019 10:43C-Suite
400030Sarah Sarahson4/08/2019 12:05Finance
400030Sarah Sarahson21/08/2019 10:43Finance
400030Jimmy James23/09/2019 10:43Sales
400030Kelly kellyson4/08/2019 12:05Finance
400030Kenny Kennyson23/09/2019 10:43Sales
400030Kenny Kennyson21/08/2019 10:43Sales
400030Mark Markson4/08/2019 12:05Sales
400030Mark Markson23/09/2019 10:43Sales
400035Michael Hutchens4/08/2019 12:05Services
400035Peter Peterson23/09/2019 10:43Services
400035Petra Petrason4/08/2019 12:05C-Suite
400035Petra Petrason21/08/2019 10:43C-Suite
400035Petra Petrason4/08/2019 12:05C-Suite
400035Petra Petrason23/09/2019 10:43C-Suite
400035Sarah Sarahson21/08/2019 10:43

Finance

 

I'd like to be able to create a table that shows the following:

 

DocIDTotal Hits Last Month% Change Since Month -2% Usage Last MonthPosition Change From Month -2
4000293+150%33%+1
4000304-66%44%--
4000352-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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

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.

View solution in original post

6 REPLIES 6
Community Support
Community Support

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

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.

View solution in original post

Anonymous
Not applicable

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

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:

 

Capture.PNG

Community Support
Community Support

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

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
Not applicable

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

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

Anonymous
Not applicable

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

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 & "%"
 
Capture.PNG
Community Support
Community Support

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

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors