cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

@Mr_ME , If want show split across days

refer

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?