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

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

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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.