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 = CALC...
Need Help
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 ()
            )
        )
)
      • Hkhalifah-DPG's avatar
        Hkhalifah-DPG
        Frequent Visitor
        4 years ago

        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