Forum Discussion
Calculate a minimum value across multiple rows based on multiple fields
You should be able to use a calculation like the following:
Measure = var _table = CALCULATETABLE(
Table1,
ALL(Table1),
VALUES(Table1[Problem Type]),
VALUES(Table1[Account]),
Table1[Monthly Running Total] >= 1000) RETURN MINX(_table, Table1[Transaction Year and Month])
Firstly I'm creating the _table varialbe using the ALL function to remove all filters from the table, then I add back just the Account and Problem Type filters as well as a filter for where the running total is >= 1000Then I using MINX to find the minimum month from this variable
This column calculation is working exactly as expected but I ran across an unexpected wrinkle in my data as I was implementing it. Occasionally the data includes negative amounts that drive the running total back down below $1,000. In those instances the column is reporting the earliest year and month in which it hit the 1K threshold but I'd like it to return no value in this new column if the most recent year and month for that problem type and account is no longer 1K or over. Similarly if it hits the 1K threshold, later dips down below it, then later rises above it (like problem type C below) I'd like it to give me the earliest year and month in which it most recently hit the 1K threshold.
This seems a lot more complicated than the problem I thought I was trying to solve. Any thoughts on how I might be able to incorporate that more complicated check?
Thanks!
Damon
Problem Type | Account | Transaction ID | Transaction Year and Month | Amount | Monthly Running Total | Min Year and Month Running Total >=1K |
A | 1 | 001 | 201901 | 500 | 500 |
|
A | 1 | 002 | 201902 | 600 | 1100 |
|
A | 1 | 003 | 201903 | -400 | 700 |
|
B | 2 | 004 | 201901 | 1000 | 1000 |
|
B | 2 | 005 | 201902 | -100 | 900 |
|
C | 3 | 006 | 201901 | 1100 | 1100 | 201903 |
C | 3 | 007 | 201902 | -200 | 900 | 201903 |
C | 3 | 007 | 201903 | 200 | 1100 | 201903 |
I think the following alteration to the calculated column expression should meet those new requirements. I've renamed some of the variables to make it easier to see what values they store. Basically I added an extra step to get the max date where the value was below 1000 then I only get the min date where it was after that date and greater than 1000. So the value should be able to go above and below 1000 multiple times and we will still only get the last date where it first went above 1000
Column = var _currentAccount = Table1[Account] var _currentProblemType = Table1[Problem Type] var _datesBelow1000 = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] < 1000) var _maxDateBelow1000 = MAXX(_datesBelow1000, Table1[Transaction Year and Month]) var _datesAbove1000 = FILTER( ALL(Table1), Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] >= 1000 && Table1[Transaction Year and Month] > _maxDateBelow1000) var _lastMinDateAbove1000 = MINX(_datesAbove1000, Table1[Transaction Year and Month]) return _lastMinDateAbove1000
Yes, that's working. Thanks again for the help and for explaining so clearly how you got there.