Forum Discussion

Ninimbus_jr's avatar
Ninimbus_jr
Frequent Visitor
4 years ago
Solved

Dinamic data bar jumping years

Hi everyone!

 

Im having an issue with a stacked column chart, as bellow:



The thing is, i have to show only 3 years, but they must be jumping each 2 years

So, the first graphic it is showing what I need to see, but it has to be dinamic, so, as example, if I select in the slicer the year 2020
the graphic must show 2020, 2018 and 2016

 


I set the first graphic static, so im changing it on the filters pane

Do anyone had to do this, or anyone could give me a hint on how to?

For this, I created a simple data set, I tried measures, custom coluns and other stuff, nothing worked so far

 

Thanks everyone!!




Need Help
Show and Tell
  • Ninimbus_jr's avatar
    Ninimbus_jr
    4 years ago
    I had to go a little further, but thanks anyway!

    This is how I solved the problem:

    VAR     entrada =
    MAX('Calendar'[ano])

    VAR tabela =
    ADDCOLUMNS(
    SUMMARIZE(
    Sheet1,
    Sheet1[Ano],
    "Valor",
    CALCULATE(SUM(Sheet1[Valor]),FILTER(Sheet1,Sheet1[Ano]<=entrada && Sheet1[Ano]>= entrada-4))),
    "even_odd",
    IF(ISEVEN([ano]),TRUE(), FALSE()))

    RETURN
    IF(ISEVEN(entrada),
    SUMX(filter(tabela,[even_odd]==TRUE()),[Valor]),
    SUMX(filter(tabela,[even_odd]==FALSE()),[Valor]))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Ninimbus_jr , Create a separate year table and do not join with the table

     

    measure =

    var _max = maxx(allselected(Year), Year[Year])

    return

    calculate(sum(Table[Value]), filter( Table, Table[Year] =_max || Table[Year] =_max -2 || Table[Year] =_max -4))

    • Ninimbus_jr's avatar
      Ninimbus_jr
      Frequent Visitor
      4 years ago
      I had to go a little further, but thanks anyway!

      This is how I solved the problem:

      VAR       entrada =
      MAX('Calendar'[ano])

      VAR tabela =
      ADDCOLUMNS(
      SUMMARIZE(
      Sheet1,
      Sheet1[Ano],
      "Valor",
      CALCULATE(SUM(Sheet1[Valor]),FILTER(Sheet1,Sheet1[Ano]<=entrada && Sheet1[Ano]>= entrada-4))),
      "even_odd",
      IF(ISEVEN([ano]),TRUE(), FALSE()))

      RETURN
      IF(ISEVEN(entrada),
      SUMX(filter(tabela,[even_odd]==TRUE()),[Valor]),
      SUMX(filter(tabela,[even_odd]==FALSE()),[Valor]))
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Ninimbus_jr,

        Thanks for sharing your solution and expressions.😊 I think they will help others who faced similar requirements.

        Regards,

        Xiaoxin Sheng