Multiple date filters in measure for table

[PowerPuff36]

Hi all,

Hoping someone can help as i have been stuck for 3 days now! 

I am trying to create a table which shows this weeks data next to the previous weeks data. Using a slicer we want to be able to change the date so the table will update. The issue i have is we need to account for multiple date filters. 

So the requirements are - 

StartDate needs to be before or equal to date selected

EndDate needs to be after or equal to date selected 

CancelledDate needs to be after or equal to date selected OR blank

UserStatus must equal active

I did think i could maybe make userelationship work and tested it with just the StartDate but it doesn't like it 

CALCULATE (COUNTROWS( UserTable ),FILTER (UserTable,UserTable[Department]), UserTable[startdate] <=USERELATIONSHIP(UserTable[startdate],'Calendar'[Date]))

I have tried so many variations through help from articles and looking through the community but no luck! Any help would be greatly appreciated! 

[v-jingzhang · Accepted solution]

Hi @PowerPuff36 

Assume your UserTable is like below and you have a Calendar table, you don't need to create a relationship between them. 

Put date column from Calendar table into a slicer for users to pick a date. Then you could create below measures to get the count on the selected date and on the date 7 days ago (previous week). You can use && (AND) and || (OR) to combine multiple filters. 

This Week Count =
VAR __selectedDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    COUNTX (
        FILTER (
            UserTable,
            UserTable[StartDate] <= __selectedDate
                && UserTable[EndDate] >= __selectedDate
                && (
                    UserTable[CancelledDate] >= __selectedDate
                        || ISBLANK ( UserTable[CancelledDate] )
                )
                && UserTable[UserStatus] = "Active"
        ),
        UserTable[UserId]
    )

Previous Week Count =
VAR __selectedDate = SELECTEDVALUE ( 'Calendar'[Date] ) - 7 
RETURN
    COUNTX (
        FILTER (
            UserTable,
            UserTable[StartDate] <= __selectedDate
                && UserTable[EndDate] >= __selectedDate
                && (
                    UserTable[CancelledDate] >= __selectedDate
                        || ISBLANK ( UserTable[CancelledDate] )
                )
                && UserTable[UserStatus] = "Active"
        ),
        UserTable[UserId]
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.