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...
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]))
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