Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dax Help

Hi, I am trying to calculate the below excel formula in Power BI as a calculated column (DAX or MQuery or Python)   Formula: IDA = ((1 + (Current value of PX_LAST/10000)) * Next IDA value)   Sam...
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    Well, never say never.

     

    Behold:

     

    IDA_m = 1+max('Table'[PX_Last])/10000

IDA_p = 
var d=max('Table'[As of Date])
return 10000000*PRODUCTX(filter(all('Table'),'Table'[As of Date]<=d),[IDA_m])
Anonymous's avatar
Anonymous
Not applicable
6 years ago

lbendlin Thank you for the response. 

 

To answer your question, by "Previous" I mean the value from previous day (same as previous row if placed in descending order by Date) . And there will not be multiple PX_LAST for any given date. 

 

Please let me know if you have any other questions. 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

"by "Previous" I mean the value from previous day (same as previous row if placed in descending order by Date)"

 

You see, this is exactly where my confusion comes from. I would call that the NEXT row. The previous row is on top of the current row, and the next row is below it - at least in my cultural perception. (I also wouldn't put dates in descending order but that's probably just me)

 

Here is how I would start:

 

var d = max('Table'[As of Date])

var prev_d = calculate (max('Table'[As of Date]),ALL('Table'),'Table'[As of Date]<d)

 

That gives you the latest date before your filter context date.

 

And now it becomes really complex since your measure depends on itself. Most likely this will lead to some sort of circular reference.

 

Where does the starting value come from? is that the 10 mil under the July line?