Forum Discussion

SammuelM's avatar
SammuelM
Helper I
2 years ago
Solved

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

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. ...
Need Help
Show and Tell
  • SammuelM's avatar
    SammuelM
    2 years ago

    Solution. Thank you all.

     

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

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
2 years ago

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's avatar
    SammuelM
    Helper I
    2 years ago

    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.