Forum Discussion

Usha_pk's avatar
Usha_pk
Icon for Helper II rankHelper II
2 years ago
Solved

How to show dynamic year based on selected year

Hi team,

 

I have a requirement for the dynamic year column where based on the selected year, it should show the previuos six years.

If the seleted year is 2030, then it should reflect 2024,2025,2026,2027,2028,2029.
If the selected year is 2029, it should show 2023,2024,2025,2026,2027,2028.

Can you please help on this. Thank you!
tamerj1 Ritaf1983 Ahmedx Greg_Deckler amitchandak Fowmy 

data protection
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Usha_pk ,

    Based on your problems, I created a simple table.

    First of all I think you can create a Measure to fulfill your needs, you can have a try.

    Dynamic Year = 
VAR SelectedYear = YEAR(TODAY())
RETURN
IF(
    VALUES('Year'[Date]) IN { SelectedYear, SelectedYear - 1, SelectedYear - 2, SelectedYear - 3, SelectedYear - 4, SelectedYear - 5 },
    1,
    0
)

    You can also select New group under Slicer.

    This will also give you the results you're looking for.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    Sales measure: = 
SUM( Sales[Sales] )

     

    Sales last 6 years: =
VAR _t =
    WINDOW ( -5, REL, 0, REL, ALL ( Period[Year] ), ORDERBY ( Period[Year], ASC ) )
VAR _condition =
    COUNTROWS ( _t ) = 6
RETURN
    IF ( _condition, CALCULATE ( [Sales measure:], _t ) )

     

    • Usha_pk's avatar
      Usha_pk
      Icon for Helper II rankHelper II
      2 years ago

      Jihwan_Kim 

      Thanks for your reply. But this is not exactly my requirement. Let me explain you clearly.
      I need a dynamic year column, where based on the year selected it should reflect the previous six years.

      For example:
      If selected year is 2028, then the dynamic year should be 2022, 2023, 2024, 2025, 2026, 2027

      Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Usha_pk ,

    Based on your problems, I created a simple table.

    First of all I think you can create a Measure to fulfill your needs, you can have a try.

    Dynamic Year = 
VAR SelectedYear = YEAR(TODAY())
RETURN
IF(
    VALUES('Year'[Date]) IN { SelectedYear, SelectedYear - 1, SelectedYear - 2, SelectedYear - 3, SelectedYear - 4, SelectedYear - 5 },
    1,
    0
)

    You can also select New group under Slicer.

    This will also give you the results you're looking for.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.