cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paolomint
Helper II
Helper II

Calculation on values on the same column

Dear All,

 

I need to make a subtraction between two values on the same Column.

 

In my table I have the quantity of each ITEM at the end of the moth.

I need to add another column with the delta between two consecutive months:

 

quantity of February - quantity of Jannuary

quantity of March - quantity of Febrary

...

 

ITEMdateQuantitydelta
V3231.01.202134 
V3228.02.20217743
V3231.03.202130-47
M3731.01.202146 
M3728.02.202128-18
M3731.03.20215527

 

PowerQuery using M or Dax in PowerBI is both ok.

Thanks in advance

Paolo

  

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@paolomint 

 

You can create a calculate column using DAX by filtering with end of previous month.

Column =
var pre_eom= ENDOFMONTH(PREVIOUSMONTH('Table'[date]))
var pre_eom_quantity = CALCULATE(SUM([Quantity]),FILTER('Table',[TEM]=EARLIER([TEM]) && [date]=pre_eom))
Return IF(ISBLANK(pre_eom_quantity),BLANK(),[Quantity]-pre_eom_quantity)
 

V-pazhen-msft_0-1623810721421.png

If you don't want the blank, just use: Return [Quantity]-pre_eom_quantity

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@paolomint 

 

You can create a calculate column using DAX by filtering with end of previous month.

Column =
var pre_eom= ENDOFMONTH(PREVIOUSMONTH('Table'[date]))
var pre_eom_quantity = CALCULATE(SUM([Quantity]),FILTER('Table',[TEM]=EARLIER([TEM]) && [date]=pre_eom))
Return IF(ISBLANK(pre_eom_quantity),BLANK(),[Quantity]-pre_eom_quantity)
 

V-pazhen-msft_0-1623810721421.png

If you don't want the blank, just use: Return [Quantity]-pre_eom_quantity

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @paolomint 

Please check the below picture and the sample pbix file's link down below for creating a new column.

 

Picture1.png

 

Delta CC =
VAR currentitem = 'Table'[ITEM]
VAR rankingbydate =
RANKX ( FILTER ( 'Table', 'Table'[ITEM] = currentitem ), 'Table'[date],, ASC )
VAR previousquantity =
MAXX (
FILTER (
'Table',
'Table'[ITEM] = currentitem
&& RANKX ( FILTER ( 'Table', 'Table'[ITEM] = currentitem ), 'Table'[date],, ASC ) = rankingbydate - 1
),
'Table'[Quantity]
)
RETURN
IF ( NOT ISBLANK ( previousquantity ), 'Table'[Quantity] - previousquantity )

 

 

https://www.dropbox.com/s/7odn75hrzh0kuho/paolomint.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors