Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
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)
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.
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)
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.
Hi @jeanramonyap,
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
22 | |
17 | |
11 | |
11 | |
10 |
User | Count |
---|---|
31 | |
25 | |
15 | |
13 | |
12 |