Average MTD - current month - nightly data refresh

[Anonymous]

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 1: May 25th - The record count (20) is the same as the MTD average

2) 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 ...

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)

Thank you!

[Greg_Deckler]

@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]

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?

[V-pazhen-msft]

@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]

I would like to clarify the dataset that I have to work with:

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.

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, 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!

6/1/2020
Posting Date	Record Count
5/1/2020	50
5/11/2020	100
5/30/2020	2
Total	152
6/2/2020
Posting Date	Record Count
5/1/2020	45
5/11/2020	100
5/30/2020	0
6/1/2020	4
Total	149

@V-pazhen-msft 

@Greg_Deckler 

[Anonymous]

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.