Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
dax
Community Support
Community Support

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
dax
Community Support
Community Support

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

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

dax
Community Support
Community Support

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

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
dax
Community Support
Community Support

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.