Forum Discussion

SammuelM's avatar
SammuelM
Icon for Helper I rankHelper 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.


| 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]
)

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]
    )

     

     

13 Replies

    • SammuelM's avatar
      SammuelM
      Icon for Helper I rankHelper I
      2 years ago

      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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    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)
    )

      • pacomase1's avatar
        pacomase1
        Frequent Visitor
        2 years ago

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

    • SammuelM's avatar
      SammuelM
      Icon for Helper I rankHelper I
      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 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]
)
  • 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
      Icon for Helper I rankHelper 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.

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

    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.