Forum Discussion
Filter DatesBetween
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
- in a column "isWorkday" I have a boolean
- I have an absence_list given from the server (screenshots below)
- here I have
- start
- end
- user_id
- here I have
- 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
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
2 Replies
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