Forum Discussion
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...
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])
We would have to list all the dates that are prior or equal to the filter context date and then have to successively apply the formula.
for 7/1/2013 it would be
(1+ (-13*1e-4))*1e7 = 1e7 + (-13*1e3)
or more generic
(1+PX_Last_7_1*1e-4)*1e7
For 8/1/13 it would be
(1+ PX_Last_8_1*1e-4) (1+PX_Last_7_1*1e-4)*1e7
For 9/1/13 it would be
(1+ PX_Last_9_1*1e-4)(1+ PX_Last_8_1*1e-4) (1+PX_Last_7_1*1e-4)*1e7
So we _should_ get away with a cumulative product of (1+PX*1e-4), and then at the very end multiply that by 10 mil.
Having said that I have no idea how to do cumulative products in Power BI. All I know is SUMX 😞
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])