Filter dates table based on start and end date and working pattern

[Hkhalifah-DPG]

Afternoon,

hoping someone can assist. I am trying to filter a datestable by both the start and end date and whether someone works on a specific day. 

So far i have this:

is between = CALCULATE(
IF(HASONEVALUE('Working pattern'[Employee])
,var vStartDate = FIRSTNONBLANK('Working pattern'[Start],1)
var vEndDate = FIRSTNONBLANK('Working pattern'[End],1)
return
SUMX('DatesTable'
,IF(AND('DatesTable'[Date] >= vStartDate,'DatesTable'[Date] <= vEndDate),1,BLANK())
)
),KEEPFILTERS(NOT 'DatesTable'[DayOfWeekName] IN {"Saturday","Sunday"}))

which gives me the working days between the start and end date depending on which employee is selected. However I need to add an additional variable to the keepfilters I think so that when i select "CS" as an employee it exlcudes Thursdays and Fridays. 

Note on worked column:

1 = they work this day

0 = they do not work this day

0.5 = they work half a day

with thanks

Hasan Khalifah

[johnt75]

try 

is between =
CALCULATE (
    IF (
        HASONEVALUE ( 'Working pattern'[Employee] ),
        VAR vStartDate =
            FIRSTNONBLANK ( 'Working pattern'[Start], 1 )
        VAR vEndDate =
            FIRSTNONBLANK ( 'Working pattern'[End], 1 )
        RETURN
            SUMX (
                'DatesTable',
                IF (
                    AND ( 'DatesTable'[Date] >= vStartDate, 'DatesTable'[Date] <= vEndDate ),
                    1,
                    BLANK ()
                )
            )
    ),
    KEEPFILTERS (
        TREATAS (
            CALCULATETABLE (
                VALUES ( 'Working pattern'[Day] ),
                'Working pattern'[Worked] > 0
            ),
            'DatesTable'[DayOfWeekName]
        )
    )
)

[Hkhalifah-DPG]

Awesome John. Thanks for this. Anychance you can help with half days aswell? 

[johnt75]

is between =
IF (
    HASONEVALUE ( 'Working pattern'[Employee] ),
    VAR vStartDate =
        FIRSTNONBLANK ( 'Working pattern'[Start], 1 )
    VAR vEndDate =
        FIRSTNONBLANK ( 'Working pattern'[End], 1 )
    RETURN
        SUMX (
            'DatesTable',
            IF (
                AND ( 'DatesTable'[Date] >= vStartDate, 'DatesTable'[Date] <= vEndDate ),
                VAR dayMultiplier =
                    LOOKUPVALUE (
                        'Working pattern'[Worked],
                        'Working pattern'[Employee], SELECTEDVALUE ( 'Working pattern'[Employee] ),
                        'Working pattern'[Start], vStartDate,
                        'Working pattern'[End], vEndDate,
                        0
                    )
                RETURN
                    1 * dayMultiplier,
                BLANK ()
            )
        )
)

[Hkhalifah-DPG]

Thanks for this John.

Apolgies I should have been far clearer with orginal data. I have now updated this. For purposes of clarity I have highligted the two lines that I am having trouble with.

To explain why I am trying to calculate this. On occasion colleagues change their working patterns for an initial period when returning maternity leave. 

So in the example of "CS" I want a measure that filters the dates table to only include  Monday,Tuesdays and Wednesdays from 01/04/2020 to 31/03/2021 and Thursdays and half of Fridays from 31/10/2020 to 31/03/2021.

Apolgies for the lack of clarity in what i was trying to achieve in my orginal post. 

With thanks

Hasan Khalifah 

[johnt75]

Try

days worked =
IF (
    ISINSCOPE ( 'Working pattern'[Employee] ),
    SUMX (
        'Working pattern',
        VAR currentDay = 'Working pattern'[Day]
        VAR currentMultiplier = 'Working pattern'[Worked]
        VAR startDate = 'Working pattern'[Start]
        VAR endDate = 'Working pattern'[End]
        VAR numDays =
            COUNTROWS (
                FILTER (
                    'Dates',
                    KEEPFILTERS ( 'Dates'[Date] >= startDate
                        && 'Dates'[Date] <= endDate
                        && 'Dates'[Day] = currentDay )
                )
            )
        RETURN
            numDays * currentMultiplier
    )
)

[Hkhalifah-DPG]

Evening John,

im getting the following error: KEEPFILTERS function can only be used as a top level filter argument of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan.

[johnt75]

Ah, right. Try

days worked =
IF (
    ISINSCOPE ( 'Working pattern'[Employee] ),
    SUMX (
        'Working pattern',
        VAR currentDay = 'Working pattern'[Day]
        VAR currentMultiplier = 'Working pattern'[Worked]
        VAR startDate = 'Working pattern'[Start]
        VAR endDate = 'Working pattern'[End]
        VAR numDays =
            COUNTROWS (
                FILTER (
                    KEEPFILTERS ( 'Dates' ),
                    'Dates'[Date] >= startDate
                        && 'Dates'[Date] <= endDate
                        && 'Dates'[Day] = currentDay
                )
            )
        RETURN
            numDays * currentMultiplier
    )
)

[Hkhalifah-DPG]

Afternoon John,

This hasnt worked but cheers for trying. I've done abit of a work around and created a measure which works up until someone has an additional entry in their working pattern. At which point it falls apart. I was wondering if you new in principle what I am trying to achieve is possible? 

I.e filtering a single dates table with multiple conditions based on the date range? 

I have tested creating two measures with two dates tables and suming the measures together and this works but its quite clunky. 

[johnt75]

Another option to think about would be creating a new table with a row for each date the employee worked then linking that to the date table. I think the logic would be something like running GENERATE over the existing table to get a row context including the start & end dates and day of the week, then for each row you would want to run FILTER over ADDCOLUMNS ... DATESBETWEEN to include only those dates which match the day of the week of the current row.