Average per year for all years prior

1
votes

I have a table with sales data starting from 1/1/2015 to current (as of today it would be 3/7/2021). I also have a separate date table that's linked to the sales date.

My total sales orders for each year from 2015 to 2020 is as follows:

  • 2015: 1847
  • 2016: 1698
  • 2017: 1134
  • 2018: 1036
  • 2019: 1127
  • 2020: 1136
  • 2021: 121

How can I calculate the average of total orders per year for the first 5 years (2015 to 2020)? The average should be 1,330 order per year. If I use the formula below it takes into account the current year (2021) which I don't want because 2021 has not been completed yet.

Total Orders = DISTINCTCOUNT('Sales'[Order])

Avg. Annual Orders = AVERAGEX(
    VALUES('Date'[Year]),
        [Total Orders])
2
powerbidax

2 Answers

1
votes

You can use this below Measure code-

average_2015_2020 = 

var total_order = 
CALCULATE(
    SUM(Sales[order]),
    FILTER(
        ALL(Sales),
        Sales[year] >= 2015 && Sales[year] <= 2020
    )
)

RETURN DIVIDE(total_order,6,0)

Here is the output-

enter image description here

Note from 2015 to 2020, its actually 6 years and as a result I have divided the total order with 6 instead of 5. You can adjust the logic yourself correctly if required.

0
votes

Assuming your Total Orders measure is working as intended, you can average previous years like this:

AvgOrderPrevYears =
VAR CurrYear = MAX ( 'Date'[Year] )
VAR PrevYears = CALCULATETABLE ( VALUES ( 'Date'[Year] ), 'Date'[Year] < CurrYear )
RETURN
    AVERAGEX ( PrevYears, [Total Orders] )

Table visual