Forum Discussion

PJC83's avatar
PJC83
New Member
4 years ago
Solved

COUNTROWS that match a certain value (date)

Good Morning,

 

I'm really struggling with this one in DAX as it'd be so simple with Excel.

 

Bascially, I have been sent a dataset with the due date of a project formatted in yyyy-mm:

 

 

How would I create a calculation to count the rows that match the current month? (2022-03) - a calculation that updates as the months pass and therefore the current month value changes.

 

Many thanks.

  • Anonymous's avatar
    Anonymous
    4 years ago

    As a shortened form of the other answer, you can use :

     

    Count of due dates this month =
COUNTROWS(FILTER(TableName, TableName[Target Date Month(If known)]= FORMAT(TODAY(),"YYYY-MM")))

     

     

2 Replies

  • Richard_100's avatar
    Richard_100
    Resolver I
    4 years ago

    Hello

     

    The answer depends on your data model set up, so at a top level you need a calculation like this one, but potentially with the VARs adjusted according to your model if you need something more sophisticated:

     

    Countrows Current Month:=
VAR CurrentYear = FORMAT(YEAR(TODAY()), "0000")
VAR CurrentMonth = FORMAT(MONTH(TODAY()), "00")
VAR CurrentYear_Month = CurrentYear & "-" & CurrentMonth

RETURN COUNTROWS(FILTER(Data, Data[Target Date Month (If Known)]=CurrentYear_Month))

     

     

     

    Hope that helps

     

    Regards

    Richard

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    As a shortened form of the other answer, you can use :

     

    Count of due dates this month =
COUNTROWS(FILTER(TableName, TableName[Target Date Month(If known)]= FORMAT(TODAY(),"YYYY-MM")))