Average of a measure by year

[AshleyMartinez]

Hi:

I want to calculate the average of a measure by a period of time, in this case by year.

I've created these measures:

Qty = SUM(Table1[Qty])

Sells = CALCULATE(SUM(Table2[SellsInUSD])*3.2)

MovingSells = CALCULATE([Sells];DATESINPERIOD(Calendar[Date];LASTDATE(Calendar[Date]);-1;YEAR))

KPI = [Qty]/[MovingSells]

So, the KPI measure uses other ones that come from different tables (Table1, Table2).

I tried this code but the result is just the same values of "KPI":

AvergKPI = AVERAGEX(VALUES(Calendar[Year];[KPI])

Year	Date	KPI	AvergKPI
2014	Ene-14	4,068	4,068
2014	Feb-14	4,368	4,368
2014	Mar-14	4,590	4,590
2014	Abr-14	4,025	4,025
2014	May-14	4,953	4,953
2014	Jun-14	4,017	4,017
2014	Jul-14	4,483	4,483
2014	Ago-14	4,174	4,174
2014	Set-14	4,451	4,451
2014	Oct-14	4,652	4,652
2014	Nov-14	4,500	4,500
2014	Dic-14	3,334	3,334
2015	Ene-15	2,722	2,722
2015	Feb-15	2,964	2,964
2015	Mar-15	3,162	3,162
2015	Abr-15	3,009	3,009
2015	May-15	3,499	3,499
2015	Jun-15	3,909	3,909
2015	Jul-15	3,512	3,512
2015	Ago-15	4,166	4,166
2015	Set-15	4,439	4,439
2015	Oct-15	3,794	3,794
2015	Nov-15	4,254	4,254
2015	Dic-15	3,319	3,319
2016	Ene-16	4,086	4,086
2016	Feb-16	3,811	3,811
2016	Mar-16	3,389	3,389
2016	Abr-16	3,796	3,796
2016	May-16	3,432	3,432
2016	Jun-16	3,302	3,302
2016	Jul-16	4,138	4,138
2016	Ago-16	3,858	3,858
2016	Set-16	3,774	3,774
2016	Oct-16	3,873	3,873
2016	Nov-16	3,998	3,998
2016	Dic-16	4,361	4,361

This is what I normally do in Excel using the AVERAGEIF function, updating the average columns manually:

Excel

Thank you in advance!

[xavirm]

Hello Ashley!

How did you solved this?

I need to calculate the average for a whole year and showing it all along the year visualization (even in the coming months).

Thanks a lot.

Regards,

[Anonymous]

Hello

I am having two issues. I hope you can help me with. 

I have my data and I want to create two measures: 

1) Yearly Cumulative - only cumulate values for this year, as in my table.

2) Average Yearly cumulative -  I want to do an average like below: 

January average: Yearly cumulative/nb months of year= 10/1

March average:Yearly cumulative/nb months of year= 60/3

...

Can you help me please?

Thank you very much 

Date	Sales	Yearly cumulative	Average yearly cumulative
jan-18	10	10
fev-18	20	30
mar-18	30	60
abr-18	40	100
mai-18	50	150
jun-18	60	210
jul-18	70	280
ago-18	80	360
set-18	90	450
out-18	100	550
nov-18	101	651
dez-18	102	753
jan-19	103	103
fev-19	104	207
mar-19	105	312
abr-19	106	418
mai-19	107	525
jun-19	108	633
jul-19	109	742
ago-19	110	852
set-19	111	963
out-19	112	1075
nov-19	113	1188
dez-19	114	1302

[v-yuta-msft]

Hi AshleyMartinez,

Based on your description, you want to aggregate a measure, right?

Please use nested aggregation and refer to case below:

https://community.powerbi.com/t5/Desktop/Two-level-aggregation-using-DAX-measures/td-p/182892

Regards,

Jimmy Tao

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149