Sum values in the two previous years and show in the actual year column in a graph

[asantos12]

Hi everyone,
I am trying to create a measure to show in a graph the volume of Sales in the two previous years. I wanna show in the 2019 column the sum of Sales from 2018 and 2017. In the data base I Just have the Sales per year individually.

[TomMartens · Accepted solution]

Hey,

assuming your table looks like this:

you can create a measure like this:

Measure = 
    var _currentYear = MAX('Table1'[Year])
    var _prevYear = _currentYear - 1
    var _prevprevYear = _currentYear -2
    return
    CALCULATE(
        SUM(Table1[Sales])
        , 'Table1'[Year] IN {_prevprevYear , _prevYear}
    )

Using the measure you can visualize the table like this:

You might want to suppress the calculation for the total line, then just use this:

Measure = 
    var _currentYear = MAX('Table1'[Year])
    var _prevYear = _currentYear - 1
    var _prevprevYear = _currentYear -2
    return
    IF(HASONEVALUE(Table1[Year])
        ,CALCULATE(
            SUM(Table1[Sales])
            --, ALL(Table1[Year])
            , 'Table1'[Year] IN {_prevprevYear , _prevYear}
        )
        , BLANK()
    )

Hopefully this provides what you are looking for.

Regards,

Tom

[asantos12]

Hi Tom, 

It worked perfectly,I really apreciate your help. My biggest difficult is to get familiar with HASONEVALUE function, I've tried something very similar with the answer that you gave me, but I just could show de value in a visual card and not in a graph.

Thank you very much,

Arthur Santos

[Anonymous]

Hello-

   Create a DAX measure.

Total = calculate(sum(this year)) + calculate(sum(last year))

Jared