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

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

san_21 

 

create a dax calculated column as follow :

 

cc = 
switch(
true() , 
NOT isblank(tbl_name[Market Value]) ,
  tbl_name[market value] ,
   tbl_name[ROR]
)

 

you can achieve the same thing using conditional column in power query . 

 

keep nb 4 empty, -->  this means null . 

so if column name -->  market value --> equals --> empty textbox -->  select column --> choose ROR column, 

else choose Market value column , 

 

 

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

 

san_21's avatar
san_21
Frequent Visitor
2 years ago

Hi Daniel,

Thanks for your solution.

 

With the help of DAX formula you provided, I m getting Market Value and ROR data in one column. 

However I want Derived market value data by multiplying ROR with previous Market Value.

In column B, Market value are missing for non-month end date, so I want to calculate those by multiplying previous market value with ROR.

 

Can you please help me with DAX custom column / measure?

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    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! 🤠

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      san_21 and sorry for the late response 

      i thought i did click on the reply button . 

      • san_21's avatar
        san_21
        Frequent Visitor
        2 years ago

        Thanks Daniel for helping me with query.