Calculate Daily Average per Month, Sum Results YTD

[fetterr]

Hi all,

I'm struggling with a clean way to calculate the daily average per month and then sum those values YTD. To better describe what I'm after.. Here is my table setup:

Daily Date	Monthly Rollup Date	Operator	Forecast Value
1/1/2023	1/1/2023	a	10
1/1/2023	1/1/2023	b	12
1/2/2023	1/1/2023	a	10
...	...		...
2/1/2023	2/1/2023	a	15

So the goal would be:

1. Calculate the average daily forecast per day across all operators by month
2. Multiply that daily average by how many days are in that given month
3. Sum each monthly total to get YTD

Let me know if this isn't detailed enough and I can add additional detail. I can see how to do this by creating a ton of measures for each month, however I am trying not to create 20 or so measures to accomplish this.

[v-stephen-msft · Accepted solution]

Hi @fetterr ,

You can create the following measures:

Average Group By Operator And Month = CALCULATE(AVERAGE('Table'[Forecast Value]),FILTER(ALLSELECTED('Table'),[Operator]=MAX('Table'[Operator])&&[Monthly Rollup Date]=MAX('Table'[Monthly Rollup Date])))

Multiply Group By Operator = 
var _day=COUNTROWS(FILTER(ALLSELECTED('Table'),[Operator]=MAX('Table'[Operator])&&[Monthly Rollup Date]=MAX('Table'[Monthly Rollup Date])))
return
_day*[Average Group By Operator And Month]

YTD Group By Operator = SUMX(FILTER(ALLSELECTED('Table'),[Operator]=MAX('Table'[Operator])&&[Monthly Rollup Date]<=MAX('Table'[Monthly Rollup Date])),[Multiply Group By Operator])

The calculations are grouped according to the Operator, if you don't want to group, just remove that part.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.