Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hierosir
New Member

Calculate and plot an Average from sales per year

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

1 ACCEPTED SOLUTION
technolog
Super User
Super User

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.

View solution in original post

1 REPLY 1
technolog
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors