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_...
  • 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

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