Forum Discussion

Hkhalifah-DPG's avatar
Hkhalifah-DPG
Frequent Visitor
4 years ago

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

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

Need Help

9 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Hkhalifah-DPG
      Frequent Visitor
      4 years ago

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

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago
        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 ()
            )
        )
)