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

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

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