How to create different range of years in power bi dax?

[adi_16]

Hi community!

I am stuck at one point in creating a different range of years. Suppose I have a year slicer and when any year is selected from it, it should create different ranges of years from the selected year to the maximum year present in the dataset.

Explaination-

If I select a year (eg 2018) from the slicer then it should be able to create a range something like this-

2018-19

2018-20

2018-21

2018-22 

If I select a year (eg 2020) from the slicer then it should be able to create a range something like this-

2020-21

2020-22

How it can be achived in dax? Any guidance and support would be very thankful.

Thanks in Advace!

[Anonymous]

Hi @adi_16 ,

Like this?

when I choose 2018, the visual will appear data between 2018-2022;

when I choose 2020, it will change to between 2020-2022.

You just need to directly create a slicer,and choose "between" as its expression. I don't think it needs to create dax.

This is my pbix file, you can refer to it.

Best regards,

Community Support Team Selina zhu

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

[adi_16]

Hi @Anonymous, Thank you for your response.

This is not what I am looking for. I need to get this different category of range.

[amitchandak]

@adi_16 , if you plan to create a table of columns based on the selected values that is not possible, You can only get measure if you already have a date or year table

You need have joined Date/Year table with you fact havin values

year FY

2018 2018-19

2019 2019-20

and independent table with years

//Date1 is independent Date table, Date is joined with Table
new measure =
var _min = maxx(allselected(Date1),Date1[Year])
var _max = Year(Today())
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))

[adi_16]

Hi @amitchandak , thank you for your response.

Can we create ranges of years as explained above? As I require the range in the visual representation.