cancel
Showing results 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

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?

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.