A set of Factors to be applied for 5 years, but always applied independently of selected year

[1up]

Hello, I have a case which I don't know how to model in Power Bi. Hope for some help!

We have a set of market share factors , say [70%, 65%, 50%, 35%, 10%] which is to be applied on gross sales to get net sales.

Let's say we have a table 'Gross Sales':

Year   |  Gross Sales

2023    100

2024    120

2025    130

2026    140

2027    150

2028    130

If year 2023 in the model is selected, then the market share numbers should be multiplied with years 2023 through 2027. If year 2024 in the model is selected, then the market share numbers should be multiplied with years 2024 through 2028, etc.. So like a relative application of the market share factors always multiplying with 70% at the year which is currently selected, then 65% etc

The case is easy to model if say year 2023 is always the base. But if the market share is hard coded to start at year 2023, 2024, 2025 ... then for example using 2024 as base year, then I will lose the first market share factor from the calculation. So it would need to be modelled in a different way.

Is the case understandable?

Thanks for help.

[v-shex-msft · Accepted solution]

HI @1up,

It sounds like you want to use slicer to achieve selector effect instead of filter effects. For this scenario, I'd like to suggest you create an unconnected table with year value and use it as source field of slicer.

Then you can write Dax formula to extract the selections and use it to check the current year and dynamic range of your table records and use it on ‘visual level filter’ to filter records.

flag =
VAR selected =
    MAX ( NewTable[Year] )
VAR currYear =
    YEAR ( MAX ( Table[Date] ) )
RETURN
    IF ( currYear >= selected && currYear <= selected + 4, "Y", "N" )

Regards,

Xiaoxin Sheng