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

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

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

        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.