- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count days between dates if specific column is > 0
Hi, I need some help
I have a query table "AllUnits" with a the columns
"ALL.ID" "User", "Start", "End", "Allocation"
In another query table "Available hours" i have columns
"Date", "Ava.Hours" , "User2"
Every "Date" have a value in "Available hours" of 8 if it is not a Weekend or Holiday. For every Employee there is the same set of day. so Many Dates (sorry there is a type fault a sunday with value of 😎
And i Have and overall "Date table" Table
The result is to count days between "StartsAt" and "EndsAt" if the date between start and end has a value in "Ava.hours" > 0
Have a meassure:
Measure = CALCULATE(
COUNT('Avaiable hours'[Ava.Hours]),
DATESBETWEEN('Date table'[Date], MAX( Allocation[start] ), MAX(Allocation[end])), ALL('Date table'),'Avaiable hours'[Ava.Hours] > 0
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"OBS: Some start and end dates can overlap"
Please explain what to do in such a scenario.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It was only an information with regards to how to identify them seperately
So a person can be allocated to Project 1 for 3 hours and Project 2 for 7 hours the same day,
In the end if i sum it up he will be booked for 10 hours in total on one day.
My main problem is after getting a list of days between the to dates how to destingues between workday or of day that is based on the other table if the value on that specific date is larger than 0.
I have managed to split this out to a list of date between the dates for each ALL.ID.No
So i need to count for each serie/list of date for a project All.ID:No the number of dates with a value higher than 0 in allocation. and write in a new column
When i have managed that i think i am good to go from there
Hope this helps a little
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"
So a person can be allocated to Project 1 for 3 hours and Project 2 for 7 hours the same day,
In the end if i sum it up he will be booked for 10 hours in total on one day. "
Still not clear.
Let's say they are assigned to project A from 9 am to 12 pm and to project B from 10am to 5pm. How many hours total?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I Have made a simple power bi file, that may help understand my problem, But how do i attach it?
What format
I have have refraised the original question hope this clarifies something
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
08-05-2024 07:50 PM | |||
06-02-2024 07:59 PM | |||
07-17-2024 02:31 PM | |||
Anonymous
| 06-24-2024 10:37 AM | ||
07-27-2024 01:37 PM |
User | Count |
---|---|
141 | |
115 | |
82 | |
63 | |
48 |