cancel
Showing results for
Did you mean:
Frequent Visitor

## Filter DatesBetween

Hey guys,

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II

## Re: Filter DatesBetween

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 2
Super User II

## Re: Filter DatesBetween

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

Frequent Visitor

## Re: Filter DatesBetween

Thank you very much!

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors