Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

30 Day average ignoring date filter

Hello a simple one I hope, I've looked at several posts and can't quite get to the answer.

 

I have a table which looks like this below.

 

 

The 30 day average metric is a rough formula which looks at how many were created, divides it by how many days worth of data there is and multiplies the result by 30.  

 

30_day_average =
DIVIDE([Created], datediff(min(PROdat_Data[SubmitDate]),max(PROdat_Data[SubmitDate]),day))*30
 
It works fine until I put it in a table like the above which has a 'last 30 days' relative filter.  Then the metric focuses only on the last 30 days and tries to average that. 
 
In this case it shows an average of 756 where the real average across the whole data set is around 250.  Is there a way to adjust my metic formula so it's not impacted by the last 30 day filter and instead looks at the whole table?
Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

      thanks for the help, didn't do what I was looking for but actually set me on the right path...I think

       

      I've got closer by finding the number of day's I'm interested in bypassing the filter with the below. 

       
      DIVIDE(
      COUNTX(filter(all(PROdat_Data),PROdat_Data[Top Tags]="Citrix XenApp - session - disconnect"),PROdat_Data[Top Tags]),
       
      datediff(calculate(Min(PROdat_Data[SubmitDate]),ALL(PROdat_Data)),calculate(Max(PROdat_Data[SubmitDate]),ALL(PROdat_Data)),day)
      )
      *30
       
      This gives the right result but is hardcoded to "Citrix XenApp - session - disconnect".  The table is a top 20 so will have different rows every time  Do you know how I could pass a variable into it to replace the "Citrix XenApp - session - disconnect" part of the code with whatever my row value is?