Dynamically show data from current selection in slicer till max year in data.

[freakinaditya]

Hello Community!

I have a business requirement where I have to show data as follows-

       

Suppose I have year slicer, when I select a year from the slicer then it should show data from selected year till current year (i.e 2017) in this case. Value will be added up in each row as shown.

If I select 2017 then it should show only 2017-18 data.

 

It should be dynamically changing with the year selected in the slicer till max of year.

Can anyone help and guide me in this?

Thanks in advance! 

[jatinjoshi098]

HI, I have a solution for this 

we can create a new table using summarize function like this 

Year =

GENERATE (

    SUMMARIZE( Summary, Summary[Year] ),

    VAR StartYear = Summary[Year]

    RETURN

        ADDCOLUMNS (

            GENERATESERIES ( StartYear, 2023 ),

            "s_year", StartYear,

            "e_year", [Value]

        )

)

and than we can use year in the slicer and put year range in table and respective so that we can get the desired result 

PFA 

Best Regards,

Jatin Joshi

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

[amitchandak]

@freakinaditya , Not very clear. But if select data for 1 year and want to show more than that , then slicer need to me on an independent date/year table

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

[freakinaditya]

Hi @amitchandak , Thank you for your response.

Actually, I wanted to display data in the same format as shown. A year and then its corresponding value.

if user select a single year (e.g 2014) then it should show all the value from the selected year till the maximum year present in the data(e.g -2018 in this case)
2014-15 #value
2014-16 #value
2014-17 #value
2014-18 #value
and if user select 2016 in  year slicer then output should be like this-
2016-17 #value
2016-18 #value

and if user select 2018(which is max year in data) in  year slicer then output should be like this-
2018-19 #value (only single entry)

Data should be represented in the same tabular format as below-

[jatinjoshi098]

HI, I have a solution for this 

we can create a new table using summarize function like this 

Year =

GENERATE (

    SUMMARIZE( Summary, Summary[Year] ),

    VAR StartYear = Summary[Year]

    RETURN

        ADDCOLUMNS (

            GENERATESERIES ( StartYear, 2023 ),

            "s_year", StartYear,

            "e_year", [Value]

        )

)

and than we can use year in the slicer and put year range in table and respective so that we can get the desired result 

PFA 

Best Regards,

Jatin Joshi

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