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

DAX for using previous value of same column in further calculation

Hello Community!

Can somebody help me with one specific DAX.
I want to use previous value in same column for further sum with value in another column. 


For more details here is simple example for what I need in excel.Previous value.PNG

 


It looks so easy, but I've been trying to figure it out how to get this for few days....

 

Thansk in advance!

 
1 ACCEPTED SOLUTION

@astojanac ,

New End Amount = 
var _max = MaxX(filter('ALL',COALESCE([Real Start Amount],0)>0),[Month])
var _maxamt = minx(filter('ALL',[Month]=_max),[Real Start Amount]+[Month Amount])
var _maxMonth = maxx(filter('ALL',[Month]<=earlier('ALL'[Month])),[Month])
return 
 if(_maxMonth<=_max, ([month Amount] + [Real Start Amount]),_maxamt+ sumx(filter('ALL','ALL'[Month]>_max && 'ALL'[Month]<=_maxMonth),[month Amount]))

 Please find attached file attached signature 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

Perhaps:

 

Column =
  MAXX(FILTER('Table',[Month] = EARLIER([Month]) - 1),[New End Amount])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hello 

 

@astojanac 

A new column like this should work

 

column =
var _max = minx(filter(Table,table[Real Start Amount]=0),Table[Month])
var _maxamt = minx(filter(Table,Table[Month]=_max),Table[New end Amount])
var _maxMonth = maxx(filter(Table,table[Month]<=earlier(table[Month])),Table[Month])
return
if(Table[Month]<=_max, [month Amount] + [Real Start Amount],[Real Start Amount], sumx(filter(Table,Table[Month]>_max && Table[Month]<=_maxMonth),[month Amount]))

 

If you can share data in a table format, we can provide exact calc

 

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hello @amitchandak ,

Hope you are doing well!


I remember you helped me a lot here so may I ask you for help with one similar issue?

 

I struggle with the dynamic dax that should use previously calculated value in new row.

On the following link you can find xlsx sample, the help I need is for 'Closing' column:
https://www.dropbox.com/s/hlnfvukew1q3ah5/Sample%20data.xlsx?dl=0

 

Thanks in advance!

 

Kind regards, 

Alex

Thank you   , but not solution yet, cause I can't use New End Amount, New End Amount is what we need. 

Here are links to simplified .pbix and .xlsx 

PowerBI Problem.pbix 

PowerBI Problem.xlsx 


My model is not simple as this, I have about 10 tables and source is not excel, but all I need now if that Dax and I will adapt it. 

 

I would be very grateful if we could find solition 😞
Thanks in advance !

littlemojopuppy
Super User
Super User

What it seems you want to do would be very easy using time intelligence calculations.  And you'll need a date table.

Without seeing the data model the DAX would be something like this

IF(
	OR(
		RealStartAmount = 0,
		ISBLANK(RealStartAmount),
	),
	CALCULATE(
		NewEndAmount,
		PREVIOUSMONTH(Calendar[Date])
	) + MonthAmount,
	RealStartAmount + MonthAmount
)

 

 

Dear littlemojopuppy,

Thanks for your answer. I have date table, but this is not solution for my problem.
I can't use NewEndAmount in calculate because NewEndAmount is what I'm looking for. I need formula for NewEndAmount. 

I can post simplified .pbix example if you can check what else I could try. 

Thanks again!

Hi.  If you look at the code I provided, it's using time intelligence to calculate the previous month's value of New End Amount which is exactly what is indicated in your Excel formula.  Try it out...

IF(
	OR(
		RealStartAmount = 0,
		ISBLANK(RealStartAmount),
	),
	CALCULATE(
		NewEndAmount,
		PREVIOUSMONTH(Calendar[Date])
	) + MonthAmount,
	RealStartAmount + MonthAmount
)

 

Thanks again littlemojopuppy, but I can't adapt your code:

IF(

OR(
RealStartAmount = 0,
ISBLANK(RealStartAmount),
),
CALCULATE(
NewEndAmount,                            -     This is part that I can't put in calculate, cause column I want to calculate is NewEndAmount
PREVIOUSMONTH(Calendar[Date])
) + MonthAmount,
RealStartAmount + MonthAmount

I posted sample in previous comment, so can I please you to check it there againg? Thanks a lot!




@astojanac ,

New End Amount = 
var _max = MaxX(filter('ALL',COALESCE([Real Start Amount],0)>0),[Month])
var _maxamt = minx(filter('ALL',[Month]=_max),[Real Start Amount]+[Month Amount])
var _maxMonth = maxx(filter('ALL',[Month]<=earlier('ALL'[Month])),[Month])
return 
 if(_maxMonth<=_max, ([month Amount] + [Real Start Amount]),_maxamt+ sumx(filter('ALL','ALL'[Month]>_max && 'ALL'[Month]<=_maxMonth),[month Amount]))

 Please find attached file attached signature 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Here you find the solution to the problem.

Dear @amitchandak,
Thank you so much. You helped me a lot!

And thanks to all of you who tried to help and to solve my problem 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!