Forum Discussion

majid154a's avatar
majid154a
Helper II
6 months ago
Solved

Year updated automatically

Dear Experts,

 I have a slicer set as a dropdown, and it is based on a date field. For example, I currently have years like 2023, 2024, and 2025. The slicer is now selected on 2025.

 When the data is updated and a new year (for example, 2026) is added, how can I make the slicer update automatically and select the latest year (2026) by default?

 


> Note: The slicer must remain in dropdown mode.

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    6 months ago

    Slicers remember their last state. When 2026 is added to the model, Power BI will add it to the list, but it will not auto-select it. So instead of using Year column in your slicer, create a new column that contains value "This Year" instead 2026(I mean current year here). Then use that field in your slicer. E.g. slicer would be:

     

    2023

    2024

    2025

    This Year

     

    • Calculated column in your Date table:
    Year Label =
IF (
    'Date'[Year] = YEAR ( TODAY() ),
    "This Year",
    FORMAT ( 'Date'[Year], "0000" )
)

     

    • Select Year Label

    • Set Sort by column → Year

  • danextian's avatar
    danextian
    6 months ago

    Hi majid154a 

    A common solution and as what others have already suggested is to substitute the current year with another text like this year but if you want to  keep the value as-is is to leverage the group by Columns property to "store a filter by using an alternate value, which represents the key of the entity." Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically. For instance, if May-24 is selected in the visual and currently has a key of 0, when June arrives, May will shift to a key of 1, and June will take the key of 0. Consequently, the slicer selection will automatically change to June. I have a tutorial on this on YouTube - https://youtu.be/iSQ9n1z9gYA

     

5 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    6 months ago

    I would suggest to create a column in the calendar

     

    Current Year =
    VAR _CurrentYear
    YEAR ( TODAY () )  ---- or YEAR ( MAX (Sales[Date] ) ), depending on how you want to define the current year

    VAR _CurrentRowDateYear = YEAR ( Calendar [Date] )

    RETURN
    IF ( _CurrentYear = _CurrentRowDateYear, "Y", "N" )


        

     

     

    Use that column in the drop down slicer selecting the value Y

     

    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

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 months ago

    Slicers remember their last state. When 2026 is added to the model, Power BI will add it to the list, but it will not auto-select it. So instead of using Year column in your slicer, create a new column that contains value "This Year" instead 2026(I mean current year here). Then use that field in your slicer. E.g. slicer would be:

     

    2023

    2024

    2025

    This Year

     

    • Calculated column in your Date table:
    Year Label =
IF (
    'Date'[Year] = YEAR ( TODAY() ),
    "This Year",
    FORMAT ( 'Date'[Year], "0000" )
)

     

    • Select Year Label

    • Set Sort by column → Year

  • danextian's avatar
    danextian
    Super User
    6 months ago

    Hi majid154a 

    A common solution and as what others have already suggested is to substitute the current year with another text like this year but if you want to  keep the value as-is is to leverage the group by Columns property to "store a filter by using an alternate value, which represents the key of the entity." Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically. For instance, if May-24 is selected in the visual and currently has a key of 0, when June arrives, May will shift to a key of 1, and June will take the key of 0. Consequently, the slicer selection will automatically change to June. I have a tutorial on this on YouTube - https://youtu.be/iSQ9n1z9gYA

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    6 months ago

    Hi majid154a,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to danextian, cengizhanarslan, FBergamaschi,  for those inputs on this thread.

    Has your issue been resolved? If the response provided by the community member danextian, cengizhanarslan, FBergamaschi,  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      6 months ago

      Hi majid154a,

      Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

      Thank you.