Forum Discussion
Average MTD - current month - nightly data refresh
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
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.
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.
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 Anonymous