Count Project Days - How Many Days Used Per Project

[smather]

Hi all

So what I'm trying to do is count the number of days. My data looks something like this:

Name	Project	Time in	Time out	Resource
John Smith	123	15/11/2019 9:00	15/11/2019 12:00	Engineering
John Smith	LUNCH	15/11/2019 12:00	15/11/2019 12:30	Lunch
John Smith	123	15/11/2019 12:30	15/11/2019 17:00	Engineering
Jane Doe	456	15/11/2019 9:00	15/11/2019 12:00	Survey
Jane Doe	LUNCH	15/11/2019 12:00	15/11/2019 12:30	Lunch
Jane Doe	456	15/11/2019 12:30	15/11/2019 17:00	Survey
Jane Doe	789	15/11/2019 17:00	15/11/2019 17:30	Management

Each person can have several entries per day. What I'd like to be able to do, is so for Project 123, count the days used, which in this case it would be 1 day, even though there's a couple of entries for it. I think I'd be reaching for the stars to get Project 456 & Project 789 to only show half a day having been done, but if anyone has any idea how to count the days to show 1 day having been used for Project 123 that would be really helpful. 

Any insight or help with this would be absolutely fab. Or ideas on a better way to do it? I've tried hours, by adding them up and dividing by a work day, but I end up getting inflated days because of OT pushing it out of a standard work day. 

[smather]

@trebgatte I'll give the link a look, though it seems to focus on hours rather than counting days, but obviously there's something there that you think will help, so I'll go over it properly! 

@v-yuta-msft apologies, looking at it I put Project 456 down as a full day according the hours, I think I meant to only type Project 789 when saying half a day 

[v-yuta-msft]

@smather ,

Each person can have several entries per day. What I'd like to be able to do, is so for Project 123, count the days used, which in this case it would be 1 day, even though there's a couple of entries for it. I think I'd be reaching for the stars to get Project 456 & Project 789 to only show half a day having been done, but if anyone has any idea how to count the days to show 1 day having been used for Project 123 that would be really helpful. 

---

I'm confused on your description, could you please charify why in Project 456, the count is half day, not 1 day? If possible, could you please also share the expected result?

Regards,

Jimmy Tao

[trebgatte]

In Power Query, you can select both dates and create a duration column. I'd use that to then create a duration total by day. Please refer to this article for more details on how to do that. https://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358

Hope this helps!

--Treb

See our Power BI blog posts at https://marqueeinsights.com/blog