DAX for using previous value of same column in further calculation

[astojanac]

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.

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

Thansk in advance!

[Syndicate_Admin]

@amitchandak could you please help me with this post I made https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/valor-anterior/m-p/3328171. It is similar to this problem but I have to multiply and divide and I do not know how to achieve it.

Thank you!!

[Greg_Deckler]

Perhaps:

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

[astojanac]

Hello Greg_Deckler,

Thanks for your suggestion. 
I can't use New End Amount, cause that is for I'am looking for. I need dax for that column (or measure). 

Thanks again!

[amitchandak]

@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

[astojanac]

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

[astojanac]

Thank you  amitchandak , 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]

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
)

[astojanac]

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!

[littlemojopuppy]

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
)

[astojanac]

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!

[amitchandak · 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 

[Syndicate_Admin]

Here you find the solution to the problem.

[astojanac]

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 🙂

[truongnguyen]

It's just modify a bit follow  amitchandak and you can use for a measure:

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]<=Max([Month])),[Month])

return

 if(_maxMonth<=_max, (SELECTEDVALUE(Plan[Month Amount]) + SELECTEDVALUE(Real_[Real Start Amount])),_maxamt+ sumx(filter('ALL','ALL'[Month]>_max && 'ALL'[Month]<=_maxMonth),[month Amount]))

[Syndicate_Admin]

Hello @astojanac

Could you share the solution you were given, please? I can't open the file and I have a similar problem that I haven't been able to resolve.

Thank you!!