Calculate dynamic average of subset of years

[ThomasSan]

Hi everyone,

I have data that only begins halfway through my total period as can be seen in this graph:

With "Sales by SFC" only beginning in 2018, the value for "SFC Sales on Total Sales" also only starts in that very same year. Also, there is a year slicer on my page in which I can restrict the current view to only a subset of years such as 2020-2022.

Now, I would like to create an average of "SFC Sales on Total Sales" that only encompasses the years 2018-2022 (creating a general average would skew the result downwards due to the 0s in the previous years). My attempt in doing so looks as follows:

SFC Sales on Total Sales (2018 onwards) = 
var skewedaverage =
DIVIDE(
    sum(SalesOnSFC[Sales by SFC]),
    sum(TotalSales[Sales]),
    blank())

var correctaverage =
calculate(
    skewedaverage,
    year('Date'[Date]) >= 2018
)

return correctaverage  

The problem with my DAX code is that it disregards my year slicer settings in its calculations. In other words, my calculated average is static. This is a problem when the slicer is set to a time frame like 2019-2021. Can therefore someone please help me modifying my code so that it calculates the average dynamically in accordance to the settings of the year slicer?

Thank you in advance!

[ValtteriN]

Hi,

Here is one way to do this:

After 2019 =

var _year = MAX('Calendar'[Year]) return

CALCULATE(SUM(SlicerYears[Value]),all(SlicerYears),SlicerYears[Year]=_year,SlicerYears[Year]>=2019)

So include var _year and ALL to your CALCULATE.

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[ThomasSan]

Thank you for your reply, @ValtteriN !

Do you mean modifying my formula so that it reads as follows

SFC Sales on Total Sales (2018 onwards) = 
var maxyear =
MAX('Date'[Year])

var skewedaverage =
DIVIDE(
    sum(SalesOnSFC[Sales by SFC]),
    sum(TotalSales[Sales]),
    blank())

var correctaverage =
calculate(
    skewedaverage,
    ALL('Date'[Year]),
    year('Date'[Date])=maxyear,
    year('Date'[Date]) >= 2018
)

return correctaverage  

?

However, in doing so, it appears that the lower bound is disregarded now (i.e. values from earlier than 2018 are included so the average is skewed down again as the 0s are now also factored in).

[ValtteriN]

Hi,

The correctaverage should reference facttable except for the maxyear variable. As you can see with my example I am referring to 'SlicerYears' which is my fact table.

[ThomasSan]

@ValtteriN 

Oh, I got what you meant. This is my code at the moment:

SFC Sales on Total Sales (2018 onwards) = 
var maxyear =
MAX('Date'[Year])

var sfcsales=
calculate(
    sum(SalesOnSFC[Sales by SFC]),
    ALL(SalesOnSFC),
    year(SalesOnSFC[date])=maxyear,
    year(SalesOnSFC[date]) >= 2018
)

var allsales=
calculate(
    sum(TotalSales[Sales]),
    ALL(TotalSales),
    year(TotalSales[date])=maxyear,
    year(TotalSales[date]) >= 2018
)

var ratio =
DIVIDE(
    sfcsales,
    allsales,
    blank()
)

return ratio   

Here is also a screenshot of my data modell regarding all three tables involved 

However, it seems that now I only get the latest value returned 

[ValtteriN]

Hi @ThomasSan ,

Let's step back a bit, now that I see your visual I have another idea. You could just include a visual level filter where year>2018 and then calculate the ratio normally. 

[ThomasSan · Accepted solution]

@ValtteriN 

That was the final key. So the following code is the one that works:

SFC Sales on Total Sales (2018 onwards) = 
var sfcsales=
calculate(
    sum(SalesOnSFC[Sales by SFC]),
    year(SalesOnSFC[date]) >= 2018
)

var allsales=
calculate(
    sum(TotalSales[Sales]),
    year(TotalSales[date]) >= 2018
)

var ratio =
DIVIDE(
    sfcsales,
    allsales,
    blank()
)

return ratio