Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DATESMTD with filter

Hi, I want a measure, count the number of ID in the last month (April 2021), where Type = "Semi" and "Complete" and Status = "Approved". Here is my Data I am using the below DAX still I dont get ri...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi Anonymous 

    I try to reproduce your problem, however, when I input your measure, it returns the correct count:

    Can you share you PBI file after removing sensitive information?

     

    OR

    Try this:

    Measure2 =
VAR _m =
    MONTH ( LASTDATE ( DATEADD ( Sheet1[Date], -1, MONTH ) ) )
RETURN
    CALCULATE (
        COUNT ( Sheet1[ID] ),
        FILTER (
            ALL ( Sheet1 ),
            ( Sheet1[Type] = "Semi"
                || Sheet1[Type] = "Completed" )
                && Sheet1[Status] = "Approved"
                && MONTH ( Sheet1[Date] ) = _m
        )
    )

    Result:

    Hope this helps.

    Best Regards,

    Community Support Team _ Tang

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

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous 

I try to reproduce your problem, however, when I input your measure, it returns the correct count:

Can you share you PBI file after removing sensitive information?

 

OR

Try this:

Measure2 =
VAR _m =
    MONTH ( LASTDATE ( DATEADD ( Sheet1[Date], -1, MONTH ) ) )
RETURN
    CALCULATE (
        COUNT ( Sheet1[ID] ),
        FILTER (
            ALL ( Sheet1 ),
            ( Sheet1[Type] = "Semi"
                || Sheet1[Type] = "Completed" )
                && Sheet1[Status] = "Approved"
                && MONTH ( Sheet1[Date] ) = _m
        )
    )

Result:

Hope this helps.

Best Regards,

Community Support Team _ Tang

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