How do I write a Dax statement that returns the current counts whenever I filter for a TEam

[SammuelM]

Below is my sample table and I'd like to write a Dax statement that returns the current count
whenever I filter for Team A or B. Ideally, the current count per team would be on a card in power BI.

| team | yearmonth | active_cnt |
|-------:|:----------|------------:|
| a | 2023-06 | 10 |
| a | 2023-07 | 15 |
| a | 2023-08 | 30 |
| b | 2023-06 | 15 |
| b | 2023-07 | 25 |
| b | 2023-08 | 30 |

Below was my attempt but it returned a blank response:

CurrentCountPerTeam =
SUMX(
FILTER(
'Query1',
'Query1'[yearmonth] = YEAR(TODAY()) * 100 + MONTH(TODAY())
),
'Query1'[active_cnt]
)

[parry2k]

@SammuelM what is not working when you add the measure that I provided in the most recent reply? if it is not working, share your pbix file with the expected output. It is very hard to work on the replies without understanding the data.

[parry2k]

@SammuelM I see, you are formatting it as YYYY-MM, which is fine. I'm still not sure what you are looking for but try this:

CurrentCountPerTeam =

SUMX(
FILTER(
'Query1',
'Query1'[yearmonth] = TODAY ()
),
'Query1'[active_cnt]
)

[SammuelM]

So, I want to create a calculation that shows the latest count per team when I filter for the that Team. So if I filter for Team A, I'd liker to see hte count for the current month and year.

[parry2k]

@SammuelM seems like yearmonth column in your table is stored as a text, if that is the case then do this:

CurrentCountPerTeam =
VAR __Current = FORMAT ( TODAY (), "YYYY-MM" )
RETURN
SUMX(
FILTER(
'Query1',
'Query1'[yearmonth] = __Current
),
'Query1'[active_cnt]
)

[SammuelM]

[SammuelM]

It's actually a year-month date. 

[Syndicate_Admin]

Try this to see how it is.

RecuentoActualPorEquipo =
CALCULATE(
SUM('Consulta1'[active_cnt]),
FILTER(
'Consulta1',
'Consulta1'[Equipo] = "a" || 'Consulta1'[Equipo] = "b"
),
LASTNONBLANK('Consulta1'[añomes], 1)
)

[SammuelM · Accepted solution]

Solution. Thank you all.

CurrentCountPerTeam =
VAR __CurrentYearMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
RETURN
SUMX(
FILTER(
'Query1',
'Query1'[cyearmonth] = __CurrentYearMonth
),
'Query1'[active_cnt]
)

[SammuelM]

@pacomase1 Thank you but Can I get an english translation please?

[pacomase1]

CurrentCountPerTeam =
SUM('Query1'[active_cnt]),
FILTER(
'Query1',
'Query1'[team] = "a" || 'Query1'[team ] = "b"
),
LASTNONBLANK('Query1'[yearmonth], 1)
)

[SammuelM]

Thank you. But I have multiples teams(roughly 40). How do I account for all 40 instead of just 'a' & 'b'. The data you in my original post was just a sample

[parry2k]

@SammuelM what do you define as a current?

[SammuelM]

@parry2k The latest month would be current. So if there was a september entry then once  I filter for a particular team I should see the count for that team for the current year & month