Forum Discussion
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 Number | Start day | End Date | Budget Hours |
| 00001111 | 01-02-2022 | 05-02-2022 | 10 |
| 00001112 | 01-01-2022 | 05-01-2022 | 15 |
| 00001113 | 18-03-2022 | 10-04-2022 | 30 |
| 00001114 | 15-06-2022 | 23-06-2022 | 50 |
So what I want to have is this.
| Task Number | Date | Hour Per Day |
| 00001111 | 01-02-2022 | 2 |
| 00001111 | 02-02-2022 | 2 |
| 00001111 | 03-02-2022 | 2 |
| 00001111 | 04-02-2022 | 2 |
| 00001111 | 05-02-2022 | 2 |
| 00001112 | 01-01-2022 | 3 |
| 00001112 | 02-01-2022 | 3 |
| 00001112 | 03-01-2022 | 3 |
| 00001112 | 04-01-2022 | 3 |
| 00001112 | 05-01-2022 | 3 |
2 Replies
- amitchandak
Super User
Anonymous , 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-9ad6a84cf5f2Measure way
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? 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)))
