Subtracting 2 rows and appending new column from the results

[Anonymous]

I have a data table in this format:

ID	Year	Month	Quantity 1	Quantity 2
1234	2024	04	100	200
1234	2024	05	250	350
5678	2024	04	-150	-200
5678	2024	05	300	300
1111	2024	04	200	300
2222	2024	05	-150	-100

I want to get the difference of quantity 1 and 2 between max month and previous month and then create a column that will display this value. It should end up with a table like this.

ID	Year	Month	Quantity 1	Quantity 2	Difference 1	Difference 2
1234	2024	04	100	200	0	0
1234	2024	05	250	350	150	150
5678	2024	04	-150	-200	0	0
5678	2024	05	300	300	450	500
1111	2024	04	200	300	0	0
2222	2024	05	-150	-100	0	0

Is this possible? I am thinking of using indexing but it's not always a guarantee that there will be a previous value.

[Anonymous · Accepted solution]

Thanks for the reply from @barritown , please allow me to provide another insight:

Hi @Anonymous ,

You can alose try below formula to create calculated column:

Difference 1 = 
VAR CurrentID = 'Table'[ID]
VAR CurrentYear = 'Table'[Year]
VAR CurrentMonth = 'Table'[Month]
VAR PreviousMonthQuantity = 
    CALCULATE(
        MAX('Table'[Quantity 1]),
        FILTER(
            'Table',
            'Table'[ID] = CurrentID &&
            'Table'[Year] = CurrentYear &&
            'Table'[Month] = CurrentMonth - 1
        )
    )
RETURN
IF(ISBLANK(PreviousMonthQuantity), 0, 'Table'[Quantity 1] - PreviousMonthQuantity)

Difference 2 = 
VAR CurrentID = 'Table'[ID]
VAR CurrentYear = 'Table'[Year]
VAR CurrentMonth = 'Table'[Month]
VAR PreviousMonthQuantity = 
    CALCULATE(
        MAX('Table'[Quantity 2]),
        FILTER(
            'Table',
            'Table'[ID] = CurrentID &&
            'Table'[Year] = CurrentYear &&
            'Table'[Month] = CurrentMonth - 1
        )
    )
RETURN
IF(ISBLANK(PreviousMonthQuantity), 0, 'Table'[Quantity 2] - PreviousMonthQuantity)

Best Regards,
Adamk Kong

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

[barritown]

Hi @Anonymous,

You can try something like this: 

Difference 1 in plain text (Difference 2 will be pretty much the same):

Difference 1 = 
VAR curID = [ID]
VAR curYear = [Year]
VAR curMonth = [Month]
VAR prevMonth = IF ( curMonth = 1, 12, curMonth - 1 )
VAR prevYear = IF ( prevMonth = 12, curYear - 1, curYear )
VAR prevQty = MAXX ( FILTER ( tbl, [ID] = curID && [Month] = prevMonth && [Year] = prevYear ), [Quantity 1] )
RETURN IF ( NOT ISBLANK ( prevQty ), [Quantity 1] - prevQty, 0 )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian