Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jeanramonyap
Helper I
Helper I

Subtracting 2 rows and appending new column from the results

I have a data table in this format:

IDYearMonthQuantity 1Quantity 2
1234202404100200
1234202405250350
5678202404-150-200
5678202405300300
1111202404200300
2222202405-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.

 

IDYearMonthQuantity 1Quantity 2Difference 1Difference 2
123420240410020000
1234202405250350150150
5678202404-150-20000
5678202405300300450500
111120240420030000
2222202405-150-10000

 

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

1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
Community Support

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

 

Hi @jeanramonyap ,

 

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)

vkongfanfmsft_0-1719379201524.png

 

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.

View solution in original post

2 REPLIES 2
v-kongfanf-msft
Community Support
Community Support

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

 

Hi @jeanramonyap ,

 

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)

vkongfanfmsft_0-1719379201524.png

 

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
Super User
Super User

Hi @jeanramonyap,

You can try something like this: 

barritown_0-1719349161791.png

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.