Getting value for previous year based on value name and year slicer

[HollyMusgrove]

I'm trying to call a value base on year (from slicer) - 1.

So far I have this but it's returning -- (when there is a value):

[Previous Year] functions and returns 2024.

walk_school_time_2024 =

VAR _PrevYear = [Previous year]

RETURN

CALCULATE(

    SELECTEDVALUE(modelling_inputs_FACT[Value]),

    modelling_inputs_FACT[InputName] = "walk_school_time",

    modelling_inputs_FACT[Year] = _PrevYear

)

Thanks.

Example data layout:

InputName	Year	Value
walk_school_time	2024	9
walk_school_time	2025	11

[johnt75]

Is the year in the slicer coming from the fact table? If it is not, then you will need to add a REMOVEFILTERS on that column, or change the measure to apply the _PrevYear variable as a filter on the column you are using in the slicer.

[HollyMusgrove]

Oh, of course. Where would I apply REMOVEFILTERS?

Actually I can just break the relationship between the challenge_trips table (where the year filter is) and the modelling_inputs table. It's not doing anything anymore.

[FBergamaschi · Accepted solution]

Hi @HollyMusgrove,

can you show please an image of the report including the slicer on the year and the visual on which you need the measure result (wlak_school_2024) and indicate the columns involved in the slicer and in the visual (and give details of the tables these columns come from)?

So where and if the REMOVEFILTERS is needed will come clear

Most likely you need this

walk_school_time_2024 =

VAR _PrevYear = [Previous year]

RETURN

CALCULATE(

    SELECTEDVALUE(modelling_inputs_FACT[Value]),

    modelling_inputs_FACT[InputName] = "walk_school_time",

    modelling_inputs_FACT[Year] = _PrevYear,
    REMOVEFILTERS ( 'calendar'[Year] )

)

or something like this but i will be more precise once I understand what I asked you above

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Thanks