Forum Discussion

freakinaditya's avatar
freakinaditya
Icon for Helper II rankHelper II
4 years ago
Solved

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

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 d...
Need Assistance
Need Help DAX
Need Help in Power Bi
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
freakinaditya
Icon for Helper II rankHelper II
4 years ago

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-

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.