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
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 

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

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

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

        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
    )
)