Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Measure Help

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=...
  • V-lianl-msft's avatar
    4 years ago

    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-p/963216