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

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.