Calculate a minimum value across multiple rows based on multiple fields

[dadorsey]

Hi,

I'm new to Power BI and struggling to figure out how to calculate a column.

My data has rows of unique transactions representing different problem transactions identified on an account. Each row shows the amount of the individual problem transaction and the year/month in which the problem transaction occurred. It also contains a running monthly total for that problem type and account. What I'm trying to do is calculate the last column shown in the sample below--identifying the earliest Year and Month for which the Monthly Running Total equals or exceeds 1,000 for a given problem type and account. If the running total for that problem type and account never equals or exceeds 1,000 I need no value returned for that column.

Based on my reading it seems like I should be able to use the CALCULATE function to find the minimum Year and Month in which the running total first hit or exceeded 1,000 for a given problem type and account, but so far my attempts to do so have all failed. 

Any suggestions would be greatly appreciated.

Thanks!

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	201903
A	1	002	201903	600	1100	201903
A	2	003	201901	800	800
B	1	004	201901	500	1000	201901
B	1	005	201901	500	1000	201901
B	2	006	201902	1200	1200	201902

[d_gosbell · Accepted solution]

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 >= 1000

Then I using MINX to find the minimum month from this variable

[dadorsey]

Brilliant! Thanks so much! That works.

If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?

Thanks again, really appreciate the help.

[d_gosbell]

---

@dadorsey wrote:

If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?

No, if you want to use something in a slicer you would need to use a column not a measure. If you wanted to do the same thing in a calculate column I would change the expression slightly. It's usually better to avoid using CALCULATE or CALCULATETABLE in column expressions if you can.

I would do something like the following for a column calc.

Column = 
var _currentAccount = Table1[Account]
var _currentProblemType = Table1[Problem Type]
var _table = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] >= 1000)
var _result = MINX(_table, Table1[Transaction Year and Month])
return _result

[dadorsey]

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

[d_gosbell]

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

[dadorsey]

Yes, that's working. Thanks again for the help and for explaining so clearly how you got there. 

[dadorsey]

Thanks again. That column calculation works as expected too. Between those two examples I can see a lot of what I was doing wrong when I was trying to get the calculation on my own.