cancel
Showing results for
Did you mean:  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. It looks so easy, but I've been trying to figure it out how to get this for few days....

1 ACCEPTED SOLUTION  Super User
``````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

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!
12 REPLIES 12  Super User

Perhaps:

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

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    Helper II

Hello  Super User

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

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!  Helper II

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

Kind regards,

Alex  Helper II

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

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 😞  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
)``````  Helper II

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

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
)``````  Helper II

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

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!  Here you find the solution to the problem.  Helper II

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 🙂  