Forum Discussion

Anonymous

Not applicable

6 years ago

Average MTD - current month - nightly data refresh

Updated: Hi! I'm trying to calculate the avergae Month To Date record counts for the following scenario: * Data set is refreshed nightly and record counts will be updated. 1) On Day ...

Tutorial Requests

Icon for Community Champion rank

Community Champion

6 years ago

Anonymous So you want a rolling average? There is a built-in quick measure for that.

Otherwise, take a look at the time intelligence functions and when those fail you, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Anonymous
Not applicable
6 years ago
I think my constraint is that:
I need the value to be updated based on data refresh. E,g, Today's count is 20 and tomorrow's 24.
So do I need to have the 20 stored some place?

E.g. This will work if you want to use it on a filtered date range.
Rolling Average_Count = AVERAGEX(FILTER(ALLSELECTED(Dates),Dates[Date] <=MAX(Dates[Date])),[DocCount])

I tried to update with current date to get MTD average but it didnt work.

Is this clear?
- Anonymous
  Not applicable
  6 years ago
  Anonymous
  
  You can try with Today():
  
  Rolling Average_Count = AVERAGEX(FILTER(ALLSELECTED(Dates),Dates[Date] <=Today(),[DocCount])
  
  Paul Zheng _ Community Support Team
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  - Anonymous
    Not applicable
    6 years ago
    
    Rolling Average_Count = AVERAGEX(FILTER(ALLSELECTED(Dates),Dates[Date] <=Today(),[DocCount])
    This gives me the average count of all docs prior to today, but the dataset only has current day values.
    
    But I need all values for the Month of May. And note, the previous days counts get updated, they are not stored any place, so I'm not how to approach this.