Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:   DocId Hit U...
Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
dax
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Perfect, dax ! You are a star :) Thank you so much.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      dax
      Community Support
      6 years ago

      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.