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

  

 

 

 

 

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.

4 Replies

  • 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.

  • 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.