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

 

My correct answer is 5

 

Thanks !

 

Rakesh

 

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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Community 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.