Forum Discussion

san_21's avatar
san_21
Frequent Visitor
2 years ago
Solved

Create a custom column

Hi, I need help in creating custom column in PowerBi same as "Calculated MV" column of below excel screenshot. If market value data is available in column B, it should pick up same data or else cal...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    san_21 

    sorry my bad, 

     

    create part_2 column : 

     

    part_2 = 
1 + 'Table'[ROR]/ 100

     

     

    create mv column : 

     

    mv = 
SWITCH(
    TRUE(),
    not ISBLANK('Table'[Market Value]) , 'Table'[Market Value],


    var date_with_lastnonblank_market_value =  
        sELECTCOLUMNS(
        LASTNONBLANK(
            FILTER(
                'Table',
                'Table'[Date]<=EARLIER('Table'[Date])
        ),
        'Table'[Market Value]
        ),'Table'[Date]
        )

    var part_2 = 
    SELECTCOLUMNS(
        WINDOW(
            0,
            ABS,
            0,
            REL ,
            FILTER(
             SUMMARIZE(
                'Table',
                 'Table'[Date],
                 'Table'[part_2]
            ) , 'Table'[Date] >=date_with_lastnonblank_market_value
            ),
            ORDERBY('Table'[Date],asc)),
        'Table'[part_2]
        )
    



    var market_value =  
    sELECTCOLUMNS(
    LASTNONBLANK(
        FILTER(
            'Table',
            'Table'[Date]<=EARLIER('Table'[Date])
    ),
    'Table'[Market Value]
    ),'Table'[Market Value]
    )


    var part_2_product =  PRODUCTX(part_2, [part_2])
    

    return    market_value * part_2_product

)

     

     

     

    let me know if this helps 

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

lbendlin's avatar
lbendlin
Super User
2 years ago

If it is a calculated column you can also do it in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RDoAgDAPQu/BNpOsGzLMQ7n8N0aARE//WvLRZa0GYVBJBDXEEsR0YV+hxWLrEzhwDNgAZZRIXKqSoTdKFTKk1T7KFqjh9Sn6JbHR95sr3Cbk79Vd8WUPNPqQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Market Value" = _t, ROR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Market Value", Currency.Type}, {"ROR", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Calculated MV", each 
[Market Value]??List.Accumulate({1..[Index]},#"Added Index"[Market Value]{0},(state,current)=>state*(1+#"Added Index"[ROR]{current}/100)),Currency.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.