Measures/calculated column: Monthly value subtract with December last year(baseline)

[Anonymous]

Hello all,

I am working on to have monthly delta value on my visualization which is in form of table. The delta value is each month sales subtract December 2021 sales.

Need help on how to get those value and how to do visualize each month data in a column.

My data looks like this:

ID	Name	Month	Sales
1	Joe	December 2021	3000
1	Joe	January 2022	5000
1	Joe	February 2022	5000
2	Imran	December 2021	2000
2	Imran	January 2022	8000
2	Imran	February 2022	7000
3	Natalie	December 2021	6000
3	Natalie	January 2022	9000
3	Natalie	February 2022	3000

and I hope someone could guide me to have this:

Where we have delta calculate (each month sales-december 2021 sales)

ID	Name	Month	Sales	Monthly Delta
1	Joe	December 2021	3000
1	Joe	January 2022	5000	2000
1	Joe	February 2022	5000	2000
2	Imran	December 2021	2000
2	Imran	January 2022	8000	6000
2	Imran	February 2022	7000	5000
3	Natalie	December 2021	6000
3	Natalie	January 2022	9000	3000
3	Natalie	February 2022	3000	-3000

Then I hope I could visualize this as:

ID	Name	December 2021 Sales	January 2022 Sales	January 2022 delta	February 2022 Sales	February 2022 delta
1	Joe	3000	5000	2000	5000	2000
2	Imran	2000	8000	6000	7000	5000
3	Natalie	6000	9000	3000	3000	-3000

Thanks in advance.

[DataInsights · Accepted solution]

@Anonymous,

Try this calculated column:

Monthly Delta = 
VAR vDec2021 =
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Month] = DATE ( 2021, 12, 1 )
    )
VAR vSales = Table1[Sales]
VAR vResult = vSales - vDec2021
RETURN
    vResult

I converted the Month column to a date (first day of month; m/d/yyyy format).