Average of Total Year should display for all Months

[krrish116]

Hi @amitchandak ,

Please help me in sort out this.

I have a table which contains 3 columns

Date	Sales 1	Sales 2	Sales1 - Sales2	Average / Year
2018-01	1,279.00	603.00	676.00	653.00
2018-02	1,315.00	563.00	752.00	653.00
2018-03	1,301.00	576.00	725.00	653.00
2018-04	1,308.00	607.00	701.00	653.00
2018-05	1,334.00	673.00	661.00	653.00
2018-06	1,326.00	642.00	684.00	653.00
2018-07	1,300.00	657.00	643.00	653.00
2018-08	1,296.00	654.00	642.00	653.00
2018-09	1,281.00	690.00	591.00	653.00
2018-10	1,273.00	687.00	586.00	653.00
2018-11	1,152.00	530.00	622.00	653.00
2018-12	1,039.00	486.00	553.00	653.00
2019-01	1,042.00	484.00	558.00	439.75
2019-02	1,059.00	520.00	539.00	439.75
2019-03	1,068.00	553.00	515.00	439.75
2019-04	1,080.00	583.00	497.00	439.75
2019-05	1,053.00	557.00	496.00	439.75
2019-06	953.00	482.00	471.00	439.75
2019-07	976.00	514.00	462.00	439.75
2019-08	922.00	469.00	453.00	439.75
2019-09	863.00	498.00	365.00	439.75
2019-10	870.00	513.00	357.00	439.75
2019-11	843.00	539.00	304.00	439.75
2019-12	825.00	565.00	260.00	439.75

Now here the problem is i have to calculate the sum of sales1-sales2 for every year individually and divide it with 12 to show average / year

ex: sum of sales1-sales2 from 2018-01 to 2018-12 is 7836 and divide it with 12 and show value 653 in Average/year in a table and graph visual.

sum of sales1-sales2 from 2019-01 to 2019-12 is 5277 and divide it with 12 and show value 439.75in Average/year in a table and graph visual.

Please help me in sortout this issue.

I have created a measure 

Average/ Year = CALCULATE(sum[Sales1-Sales2], DATESYTD('Date'[Date]))

but it is not working out.

Please help me.

Thanks,

Krish....

[v-easonf-msft · Accepted solution]

Hi, @krrish116 

You can try calculated columns as below:

Year = YEAR('Table'[Date])

Average / Year = CALCULATE(SUM('Table'[Sales1 - Sales2]),ALLEXCEPT('Table','Table'[Year]))/12

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@krrish116 , Try a new measure like

CALCULATE(AverageX(values('Date'[Month Year]) ,calculate(sum(Table[Sales1])-SUm(Table[Sales2]))), DATESYTD('Date'[Date]))

[krrish116]

It is not working ..

I have to show same average value for all 12 months ..

ex: sum of sales1-sales2 from 2018-01 to 2018-12 is 7836 and divide it with 12 and show value 653 in Average/year in a table and graph visual.

sum of sales1-sales2 from 2019-01 to 2019-12 is 5277 and divide it with 12 and show value 439.75in Average/year in a table and graph visual.

[amitchandak]

@krrish116 , Rolling 12

example 

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,Month ))

Rolling 12 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))