Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sv12
Helper III
Helper III

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)

 

Sample Data with calculation:

As of DatePX_LastIDA
1/11/2013110,066,030.73
1/10/20134110,065,024.23
1/9/20134310,023,926.13
1/8/2013-69,981,007.80
1/7/2013-139,987,000.00
  10000000

 

Any help would be greatly appreciated! 

 

Thasnk,

SV

1 ACCEPTED SOLUTION

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])

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

Please define "previous".  Previous day, or previous row in the visual? What if there are multiple PX_LAST values for a date? 

@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. 

"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?

 

 

@lbendlin thanks for your input and yes you are right. That would be the next row. Dint realise we could see the same thing from either way, my bad. 

 

Yeah the complex part would be tackling the circular reference. 

 

The starting value (10 mil) was just entered manually in the spread sheet to start the base calculation. The first value will have to refer to this 10 mil in the calculation. 

 

Thanks,

SV

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])

@lbendlin i was not able recreate your results. Would it be possible for you to share a Power BI test file?

 

Thansk,

SV

create a new blank query, call it "Table" 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcrBEcAgCAXRXjwT8gGDWotj/21EHfGwe3q9J3lFXoVYoiQrENwJBi6WBm2BEPmSj6CZNUi7wo5Qo6bOEqKGeHyuUatCQOGKA8oF+0uUKcCYYvw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"As of Date" = _t, PX_Last = _t, IDA = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"As of Date", type date}, {"PX_Last", Int64.Type}, {"IDA", type number}})
in
#"Changed Type"

 

Then create the measures as above.

Lastly add everything to a table visual.

 

Annotation 2020-06-22 123410.png

 

 

 

@lbendlin Thank you!! That worked.

Thank you for giving me the opportunity to learn about PRODUCTX().

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.