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])
lbendlin's avatar
lbendlin
Super User
6 years ago

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 😞

 

 

 

 

 

 

 

 

lbendlin's avatar
lbendlin
Super User
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 i was not able recreate your results. Would it be possible for you to share a Power BI test file?

     

    Thansk,

    SV

    • lbendlin's avatar
      lbendlin
      Super User
      6 years ago

      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.

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        lbendlin Thank you!! That worked.