Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mr_ME
Regular Visitor

Calculate time per day based between two dates

Hi All, 

 

I am a bit stuck in my process. I got a data set what shows me a start date & end date and the expected hours that I would spend on a taks. 

 

Now I want to know how much time I would spend on a day on that task. I have tried a 100 of things. But Im not able to get the data correct visable for a grafic.

 

Task NumberStart dayEnd DateBudget Hours
0000111101-02-202205-02-202210
0000111201-01-202205-01-202215
0000111318-03-202210-04-202230
0000111415-06-202223-06-202250


So what I want to have is this. 

Task NumberDateHour Per Day
0000111101-02-20222
0000111102-02-20222
0000111103-02-20222
0000111104-02-20222
0000111105-02-20222
0000111201-01-20223
0000111202-01-20223
0000111203-01-20223
0000111204-01-20223
0000111205-01-20223
2 REPLIES 2
Mr_ME
Regular Visitor

@amitchandak thanks for your help and response. It partly helped. 

The only issue that I still have it that is counting all the days. For us Saturday and Sunday is a weekend day and not a working day. So need to exclude them.

 

Below you can see the Measure that I have used.

 

Hours Per Working day = CALCULATE(
SUMX(
SUMMARIZE(
filter(
CROSSJOIN('Ticket','Calendar Table'),
'Calendar Table'[Date] >= 'Ticket'[Start Date] &&
'Calendar Table'[Date]<= 'Ticket'[End Date])
, 'Ticket'[Number]
, 'Calendar Table'[Date]
, 'Ticket'[Budget Hours]
, 'Ticket'[Start Date]
, 'Ticket'[End Date])
, DIVIDE('Ticket'[Budget Hours]
,DATEDIFF ('Ticket'[Start Date],
'Ticketing Tool''Ticket'[End Date],day)
+1)
)
)
amitchandak
Super User
Super User

@Mr_ME , If want show split across days

 

refer

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

 

 

In case you just need diff

new column= datediff([Start Day], [End Date], day)

 

 

If this does not help
Can you share  sample output in table format?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors