YTD Average of a YoY% Measure

[Anonymous]

Hello. I am struggling with a certain calculation for a measure. I have a table which has data like below:

Date	Name	Value
11/1/2019	Gas	128
12/1/2019	Gas	111
1/1/2020	Gas	89
2/1/2020	Gas	131
11/1/2019	Solid	330
12/1/2019	Solid	364
1/1/2020	Solid	396
2/1/2020	Solid	412

I would like to create a YTD Average of Year over year % calculation. I made the YoY% as a measure, however it seems I cannot use a measure for average in another measure.

What I had so far was:

CALCULATE(

AVERAGE('Commodities'[YoY%]) ,

DATESYTD( 'Dim_Calendar'[CALENDAR_DATE].[Date])

)

I am not quite sure how to utilize DAX to create this measure. Any help would be appreciated. 

Thanks!

[v-yuta-msft]

@Anonymous ,

Can you change the measure YoY% to calculate column? If you can't, could you show the expression of this measure?

Regards,

Jimmy Tao

[Anonymous]

You can specify the "aggregation level" to calculate the average something like this:

CALCULATE(

AVERAGEX(VALUES('Commodities[Name]), 'Commodities'[YoY%]) ,

DATESYTD( 'Dim_Calendar'[CALENDAR_DATE].[Date])

)

It will calculate the YoY% for each commodity and then take the average.

Best Regards

// Ulf