Forum Discussion

HollyMusgrove's avatar
HollyMusgrove
Frequent Visitor
5 months ago
Solved

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

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:
InputNameYearValue
walk_school_time20249
walk_school_time202511
  • FBergamaschi's avatar
    FBergamaschi
    5 months ago

    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

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    5 months ago

    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's avatar
      HollyMusgrove
      Frequent Visitor
      5 months ago

      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's avatar
        FBergamaschi
        Icon for Super User rankSuper User
        5 months ago

        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