Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
@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!!
Perhaps:
Column =
MAXX(FILTER('Table',[Month] = EARLIER([Month]) - 1),[New End Amount])
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!
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
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 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
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 !
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!
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
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 🙂
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |