How to show dynamic year based on selected year

[Usha_pk]

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 

[Anonymous · Accepted solution]

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.

[Ahmedx]

refer to the following

https://www.youtube.com/watch?si=ULCpvEqe2ozkfaVi&v=iiNDq2VrZPY&feature=youtu.be

https://www.youtube.com/watch?si=LpQZK0wjFSGE-Q7-&v=ilTeVH2Cwck&feature=youtu.be

[Jihwan_Kim]

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]

@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!