DAX Measure Help

[Anonymous]

Hi Experts,

I would like to create 3 DAX measure for below content. Any one please help.

1) AUM_MAR : Sum of market_value_usd per Customer_No for latest BusinessDate

2) Bal_Fun : If Fin_Level2= “Funds” than Sum of market_value_in_usd per Customer_No for latest BusinessDate

3) Bal_Dep :  If Fin_Level2= “Deposits and Current Account” than Sum of market_value_in_usd per Customer_No for latest BusinessDate.

Thank you.

[V-lianl-msft · Accepted solution]

Please try measures like below:

AUM_MAR =
CALCULATE (
    SUM ( 'Table'[market_value_usd] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer_No] ),
        'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
    )
)

Bal_Fun =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Funds"
        )
    )

Bal_Dep =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Deposits and Current Account"
        )
    )

If the problem persists, please provide dummy data based on your data model.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[Anonymous]

@ryan_mayu 

This data is private. Not able to share it. If you able to share the DAX for it. It will be great. Thank you

[ryan_mayu]

@Anonymous 

could you pls provide some dummy data?

[ryan_mayu]

@Anonymous 

could you pls provde the sample data and expected output?