Forum Discussion
Datafruit
Helper I
5 years agoSummarize 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...
- 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
daxer-almighty
Solution Sage
5 years agoI 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
- Datafruit5 years ago
Helper I
daxer-almighty Thanks for your mightiness in DAX 🙂
I used your logic and it works. Only changed the LastDateInContext from the date table date to my fact table datetime and it worked.
Thanks a lot!