average sales per month per year

[alevandenes]

Hi all,

i am trying to obtain with a measure the average sales per month per a specific year. 

In particular:

- if the year is finished, i want to SUM all the sales per a specific year and then divide it by 12 

- if the year is still ongoing, i want to SUM all the sales for the months passed then divide that number by the number of months passed. for example, if the measure is calculate today i want to sum sales of 2021 divided 6 (months inc June).

the formula i used below is: 

Avg sales per month 2019 = CALCULATE(([sales]/12), YEAR(DATE'[Date])=2019)

It is working for 2019 but not for 2020 and 2021 - any suggestions on how to make this a rolling formula with the change of the year? 

[alevandenes · Accepted solution]

I actually solved this myself creating the "virtual" table below

Table 3 = SUMMARIZE(SALES, 'Legend - DATE'[Year], "sales", [sales], "average", if('Legend - DATE'[Year]<YEAR(today()), [sales]/12, ([sales]/(MONTH(TODAY())-1))))

[ryan_mayu]

@alevandenes 

maybe you can try this

Measure = CALCULATE(Sales[Sales-total],ALLEXCEPT('Table','Table'[year]))/12

pls see the attachment below

[amitchandak]

@alevandenes , Try like 

This year Sales = CALCULATE(([Sales Amount])/12,DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

or

This year Sales = CALCULATE(averagex(values('Date'[Month year]), [Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

[alevandenes]

they do not work 😞