Beginner Help - Want to add variance column to table

[mdonawho]

I am trying to figure out how to add a variance column (difference between 2021 and 2022 for March would be -20) to a table.  Any help would be appreciated.

[v-jianboli-msft · Accepted solution]

Hi @mdonawho ,

It seems that you are using a Matrix visual?

If so please try:

Rename the Column Subtotal:

Then apply the measure to the value field:

Measure =
IF (
    ISINSCOPE ( 'Table (2)'[Year] ),
    SUM ( 'Table (2)'[Value] ),
    CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2022 ) )
        - CALCULATE ( SUM ( 'Table (2)'[Value] ), FILTER ( 'Table (2)', [Year] = 2021 ) )
)

Output:

Another solution:

You can Pivot your column:

Output:

Then create a measure like @negi007  mentioned :

Varience = SUM('Table'[2022]) - SUM('Table'[2021])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@mdonawho in this case you can create below measures

2021_value = CALCULATE(SUM(sales[value]),YEAR=2021)

2022_value = CALCULATE(SUM(sales[value]),YEAR=2022)

variance = [2022_value]-[2021_value]

you can then use these measures in your matrix to view value for each of the measure

[amitchandak]

@mdonawho , Create MOYM measure and use that in column total using isinscope

measure = if(isinscope(Table[Year]), [MTD], [MOYM])

Measure using time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MOYN =[MTD Sales] - [last year MTD Sales]

[mdonawho]

Thank you for your reply.  I am not understanging the steps you are directing me to take.