Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to filter and count ?

I want to count month and year only (month=3 and year =2021) and status = completed

How I edit from DAX as below ?

 

Count of Status Date = VAR __CurrentMeasure =
CALCULATE(
COUNTA('Update'[Status Date]),
eomonth('Update'[Status Date],-1)+1 = DATE(2021, 3, 1)
)
RETURN
IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi Anonymous ,


    According to your description, The following measure may be helpful to you:

    Count of StatusDate =
CALCULATE (
    COUNTROWS ( 'Update' ),
    FILTER (
        'Update',
        YEAR ( [Status Date] ) = 2021
            && MONTH ( [Status Date] ) = 3
            && [Status] = "completed"
    )
) + 0

    The final output is shown below:

    Best Regards,
    Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a measure like

     

    Count of Status Date = VAR __CurrentMeasure =
    CALCULATE(
    COUNTA('Update'[Status Date]),filter('Update' ,
    eomonth('Update'[Status Date],0) = eomonth(today(),0) && 'Update'[status] = "completed")
    )
    RETURN
    IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi Anonymous ,


    According to your description, The following measure may be helpful to you:

    Count of StatusDate =
CALCULATE (
    COUNTROWS ( 'Update' ),
    FILTER (
        'Update',
        YEAR ( [Status Date] ) = 2021
            && MONTH ( [Status Date] ) = 3
            && [Status] = "completed"
    )
) + 0

    The final output is shown below:

    Best Regards,
    Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.