Filter DatesBetween

[Anonymous]

Hey guys,

I've apparently not found a similar post and kind of been banging my head against the wall about this.

What am I trying to accomplish?

• find out how many days a worker has been absent
  • being able to filter this (by week, month etc.)
  • do multiple calculations with this
  • absence score obviously affects multiple calculations
    • provisions
    • statistics on sickness etc. etc.

How have I been trying to solve this?

• in a DimDates Table I have introduced a test for weekends and holidays (which come from external)
  • in a column "isWorkday" I have a boolean
    • gives me 1 for workday, 0 if not
  • this works so far
• I have an absence_list given from the server (screenshots below)
  • here I have
    • start
    • end
    • user_id
• I now tried to calculate the absent days
  • Summing up the number of "isWorkday" in the timeframe (DATESBETWEEN) "start" and "end"
  • it now gives me the correct number of absent workdays without holidays and weekends
• apparently I am not able to filter this
  • since its month wise, I will have to be able to find out how many days a user_id has missed (example:) last month
  • if a user has been absent from (example:) 22. Januar UNTIL 6. February, this would have to be splitted

I hope this was not too complicated in explaination, dont know how to solve this at all anymore.

Screenshots:

DimDates

absence_list

[tex628 · Accepted solution]

Hello,

Generally when I have date intervals of any kind I always try to expand them:
Starting table

Add a column for the duration:
[End]-[Start]

Then add a list of dates dependant on the start end and duration:

List.Dates([Start],[Duration]+1,#duration(1, 0, 0, 0))

Expand the list and you have a row for each day of each interval:


By taking this approach you should always get the exact number of days as each row can be individually counted! 

Hope this helps!

Br,
J

[Anonymous]

Thank you very much!

I'll mark as solution as soon as I've tested out!