Forum Discussion

Datafruit's avatar
Datafruit
Icon for Helper I rankHelper I
5 years ago
Solved

Summarize a measure values

Hi  I hope you can help me, been driving myself nuts with this while trying a whole lot of different options, searching for new ideas, etc., etc. But here goes, I have this measure, that works gr...
  • daxer-almighty's avatar
    5 years ago

    I don't know your model, so I had to write such a mesaure in a model that I imagine there should be. A correct model.

     

    // Your formula is incorrect for several reasons.
    // One of them is that such a measure must return
    // a number, not text. Also, your model needs to 
    // be correct (think: star schema) to carry out
    // correct and fast calculations.
    
    ATM Balance =
    var LastDateInContext = MAX( 'Dates'[Date] )
    var Result =
        SUMX(
            DISTINCT( ATM[AtmID] ),
            // For each atm you should get the latest
            // amount in the current context, assuming
            // that 'Dates'[Date] is the column that
            // joins to your fact table (NetDailyTransactions)
            // on some NetDailyTransactions[Date] field.
            // I assume that the fact table
            // stores the net amount found in each atm
            // for any days where there were withdrawals
            // or fill-ups. Let's assume that the field
            // NetDailyTransactions[NetAmount] stores the
            // amount in the atm in question as recorded
            // at the end of such a day.
            MAXX(
                TOPN(1,
                    CALCULATETABLE(
                        NetDailyTransactions,
                        // 'Dates' must be a proper date table
                        // in the model. See dax.guide/dateadd
                        // for guidance on how to build such
                        // a table.
                        'Dates'[Date] <= LastDateInContext,
                        ALLEXCEPT( NetDailyTransactions, ATM )
                    ),
                    NetDailyTransactions[Date],
                    DESC
                ),
                NetDailyTransactions[NetAmount]
            )
        )
    return
        Result