Forum Discussion
SumIf 2 tables
Hi everyone, I'm trying to replicate a table from excel to powerbi, table1 a1 1 a2 3 a3 3 a4 5 a5 10 table 2 min >= max < sumif 1 5 7 5 20 15 ...
Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text
sumif = VAR _last_date = MAX('Table 1'[Month]) RETURN SUMX( FILTER( 'Table 1', 'Table 1'[Value] >= 'Table 2'[Min >=] && 'Table 1'[Value] < 'Table 2'[Max <] && 'Table 1'[Month] = _last_date ), 'Table 1'[Value] )
the table 2 should become since it would only sum the data for April 2022
| Min >= | Max < | Sumif |
| 1 | 5 | 0 |
| 5 | 20 | 10 |
SpartaBI
Community Champion
Community Champion
Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text
sumif =
VAR _last_date = MAX('Table 1'[Month])
RETURN
SUMX(
FILTER(
'Table 1',
'Table 1'[Value] >= 'Table 2'[Min >=]
&& 'Table 1'[Value] < 'Table 2'[Max <]
&& 'Table 1'[Month] = _last_date
),
'Table 1'[Value]
)