Average MTD - current month - nightly data refresh

Question

Updated:&nbsp;&nbsp;Hi!&nbsp;I'm trying to calculate the avergae Month To Date record counts for the following scenario:&nbsp;* Data set is refreshed nightly and record counts will be updated.&nbsp;1) On Day 1: May 25th - The record count (20) is the same as the MTD average2) On Day 2: May 26th - The record count is updated (16). Now MTD is average of previous day and current day counts.3) On Day 3: May 27th - The record count is updated (24). Now MTD is average of previous 2 days and current day counts etc..4) This continues till May 31st. The value on May 31st will be retained as the average value of May.5) On June 1st, again the current day count = MTD average and it continues #2 ...&nbsp;Current DateDaily Record CountMonthly Avg CountMonthly Avg Count (Formula in Excel)5/25/20202020=SUM($D$2:$D2)/COUNT($D$2:$D2)5/26/20201618=SUM($D$2:$D3)/COUNT($D$2:$D3)5/27/20202420=SUM($D$2:$D4)/COUNT($D$2:$D4)5/28/20202020=SUM($D$2:$D5)/COUNT($D$2:$D5)5/29/20202220=SUM($D$2:$D6)/COUNT($D$2:$D6)5/30/20202221=SUM($D$2:$D7)/COUNT($D$2:$D7)5/31/20202221=SUM($D$2:$D8)/COUNT($D$2:$D8)6/1/20202424=SUM($D$9:$D9)/COUNT($D$9:$D9)6/2/20202022=SUM($D$9:$D10)/COUNT($D$9:$D10)6/3/20202221=SUM($D$9:$D11)/COUNT($D$9:$D11)&nbsp;Thank you!

greg_deckler · Answer

Anonymous&nbsp;So you want a rolling average? There is a built-in quick measure for that.
&nbsp;
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 · Answer

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.&nbsp;So do I need to have the 20 stored some place?&nbsp;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] &lt;=MAX(Dates[Date])),[DocCount])&nbsp;I tried to update with current date to get MTD average but it didnt work.&nbsp;&nbsp;Is this clear?&nbsp;&nbsp;

anonymous · Answer

Anonymous&nbsp;You can try with Today():&nbsp;&nbsp;Rolling Average_Count = AVERAGEX(FILTER(ALLSELECTED(Dates),Dates[Date] &lt;=Today(),[DocCount])&nbsp;&nbsp;Paul Zheng _ Community Support TeamIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

&nbsp;Rolling Average_Count = AVERAGEX(FILTER(ALLSELECTED(Dates),Dates[Date] &lt;=Today(),[DocCount])This gives me the average count of all docs prior to today, but the dataset only has current day values.&nbsp;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.

anonymous · Answer

I would like to clarify the dataset that I have to work with:&nbsp;I only have Posting date of the records, but they are immaterial to me. What I need is the total record count for the day.&nbsp;So on June 1st, total is 152. Now, some records get closed out and new ones comes in and the total on June 2nd is updated to 149.To get the monthly average, I need to do (152+149)/2. However,&nbsp;152 is not stored anywhere. So. I'm unsure on how to approach this.Do I create a temp table to store daily counts for each day? TIA!&nbsp;6/1/2020&nbsp;Posting Date&nbsp;Record Count5/1/2020505/11/20201005/30/20202Total152&nbsp;&nbsp;6/2/2020&nbsp;Posting Date&nbsp;Record Count5/1/2020455/11/20201005/30/202006/1/20204Total149&nbsp;Anonymous&nbsp;Greg_Deckler&nbsp;

Forum Discussion

Average MTD - current month - nightly data refresh

5 Replies

Related content

View Nightly Refresh Busting

Average and Moving Average

Weekly Average

Accumulated average

moving average help

Current Date	Daily Record Count	Monthly Avg Count	Monthly Avg Count (Formula in Excel)
5/25/2020	20	20	=SUM($D$2:$D2)/COUNT($D$2:$D2)
5/26/2020	16	18	=SUM($D$2:$D3)/COUNT($D$2:$D3)
5/27/2020	24	20	=SUM($D$2:$D4)/COUNT($D$2:$D4)
5/28/2020	20	20	=SUM($D$2:$D5)/COUNT($D$2:$D5)
5/29/2020	22	20	=SUM($D$2:$D6)/COUNT($D$2:$D6)
5/30/2020	22	21	=SUM($D$2:$D7)/COUNT($D$2:$D7)
5/31/2020	22	21	=SUM($D$2:$D8)/COUNT($D$2:$D8)
6/1/2020	24	24	=SUM($D$9:$D9)/COUNT($D$9:$D9)
6/2/2020	20	22	=SUM($D$9:$D10)/COUNT($D$9:$D10)
6/3/2020	22	21	=SUM($D$9:$D11)/COUNT($D$9:$D11)