Create a measure that text filters and counts by both past and present dates

[water-guy-5]

Hello, I have a list of dates (both past and upcoming) that I am hoping to provide COUNT expressions on or something of that nature based on the status of the service job. Below is some dummy data.

Basically, I am trying to write a couple of measure that will appear on Cards and give basic indiciators of progress or completion.
Measure 1 - I would like an expression that counts a row when (a. the end date is past the current date and b. the 'Status' column contains any text that is not "closed" - I believe it is something along the lines of <>"closed" but I do not  know exactly the input. (I attached the status column to help demonstrate that there are multiple entries for status so I would prefer some sort of "does not contain" text filter). So basically, = Count(if(End date = >today(), staus<>"closed"). I know this is incorret, but just giving you an idea. 

Measure 2 - I would like an expression that counts the number of rows complete. Essentially, the start date needs to be within 30 days of the current date and the 'Status' column needs to return "closed". Basically, finished jobs within the last month. 

Measure 3 - I would like an expression that lets me know about upcoming start dates in the next 30 days from current date. Start date 30 days or less form current date.

Measure 4 - I would like an expression that lets me know about upcoming start dates in the next 7 days from current date. Start date 7 days or less form current date.

Thank you SO much in advance if you take the time to help me out. These feel simple, just getting the formatting with dates a little wrong. Cheers! 😀

[v-luwang-msft · Accepted solution]

Hi  @water-guy-5 ,

base table:

Try like below:

measure1: 

Measure1 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[End Date] >= TODAY ()
            && 'Table'[Status] <> "Closed"
    )
)

measure2:

Measure2 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[End Date] >= TODAY ()-30
            && 'Table'[Status] = "Closed"
    )
)

measure 3 and meausre 4 need to calculae new table,if use in base table ,will return the below error:

measure :

Measure33 = 
CALCULATETABLE( 
    VALUES('Table'[Start Date]),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start Date] >= TODAY ()
            && 'Table'[Start Date] <=TODAY ()+30
    ))

And measure 4:

Measure4 = 
CALCULATETABLE( 
    VALUES('Table'[Start Date]),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start Date] >= TODAY ()
            && 'Table'[Start Date] <=TODAY ()+7
    ))

Final will get:

Wish it is helpful for you!

Best Regards

Lucien

[water-guy-5]

Had to do a bit of tweaking on my end since I forgot to include all of my information (different info from different tables) but all in all it worked!!!! Thanks so much!