Calculate and plot an Average from sales per year

[hierosir]

Hi guys!

I have a measure that is a TransactionAmount.  I then have Financial Year Codes (FY14, FY15, FY16, FY17).  I have a bar chart which will show the total sum of TransactionAmount for each Financial Year.  Essentially showing total sales for each year.

I want to create a line to go across the graph which will show the average yearly sales.  As the Chart does show the current financial years sales (still underway), I want to have this average calculated from all PRIOR financial years, not including the current (FY17).

How can I make this value?

Values in question here:

Table: viw_rprt_transactions

Measure: TransactionAmount

Column: FinYear_Code

Chance for some help :D?

Cheers,

Hierosir

[technolog · Accepted solution]

To achieve this, you'll need a DAX measure that calculates the average sales of all prior financial years except the current year.

Here's how you can create the measure:

First, determine the maximum year present in your data. This way, you'll be able to exclude it from the average.

Next, calculate the average based on your exclusion criteria.

Here's a potential DAX measure for that:

Average Past Years Sales =
VAR CurrentYear = MAX( viw_rprt_transactions[FinYear_Code] )
RETURN
CALCULATE(
AVERAGE( viw_rprt_transactions[TransactionAmount] ),
FILTER(
viw_rprt_transactions,
viw_rprt_transactions[FinYear_Code] < CurrentYear
)
)
Explanation:

The VAR keyword allows us to define a local variable CurrentYear, which is the latest year in the FinYear_Code column.
The RETURN keyword specifies what the measure should return.
CALCULATE changes the filter context for its calculation. Inside it, we calculate the average of TransactionAmount.
The FILTER function allows us to filter the table so that we only include rows where the financial year is less than the current year.
Once you've created the measure, you can plot it as a line on your bar chart to see the average yearly sales from prior years.