DATESMTD with filter

[Anonymous]

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 right answer.

My correct answer is 5

Thanks !

Rakesh

[v-xiaotang · Accepted solution]

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.

[amitchandak]

@Anonymous , Prefer to use a date table for time intelligence and try like

calculate(count(Sheet1[ID]), datesmtd(dateadd('Date'[Date],-1, month)), filter(Sheet1, Sheet1[Type] in{ "Semi" , "Complete"} &&Sheet1[Status] = "Approved"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

@amitchandak ,

This is also not working ,