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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help with Dates

Greetings everyone,

 

I have a list on the sharepoint where people put their time working during the day.

 

Basically, the database looks like this:

 

Timesheet

DateHoursResourceActivity
01/01/20208R1A1
01/01/20207R2A2
01/01/20201R2A1
02/01/202010R2A3

 

I have another base that tells how many hours a day each resource has to work:

 

TabResources

ResourceHours per day
R18
R28

 

I created a new table using Summarize to combine the hours, and also to add Hours per day usingRelated.

 

Summary = SUMMARIZE(Timesheet;Timesheet[Resource];Timesheet[Date];"Total Hours";SUM(Timesheet[Hours]))
Hours per day = RELATED(TabResources[Hours per day])

 

 

And created a matrix using Resources, Date and Total Hours to show how many hours each resource has input in a day.

 

Also, I created 2 cards. One with Total Hours and other with Hours per day.

 

But here's where my problem starts...

 

Considering the example, on 01/01/2020 R1 and R2 worked 8h each (16 hours in total). On 01/02/2020 only R2 worked, 10 hours.

The card "Total Hours" works fine...It shows the total hours entered for all resources. It shows 26 hours as total.

 

But the card "Hours per day" has a problem: It should show 32 hours:

  • 8 hours of R1 in 01/01/2020
  • 8 hours of R2 in 01/01/2020
  • 8 hours of R1 in 02/01/2020
  • 8 hours of R2 in 02/01/2020

But as R1 did not enter any data on 02/01/2020, the card does not consider the 8h that R1 should have been worked. So, it shows only 24h.

 

How can I solve this problem?

 

Best Regards,

 

 

1 ACCEPTED SOLUTION

@Anonymous solution is attached, you can take it from here.

 

Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@Anonymous does that mean everyday each Resources hours get added regardless they worked or not? On 03rd, if R3 comes with 8 hrs per day , does that mean it will added 24 hours for 03rd (8 hours each R1, R2 and R3. Is this the business logic? Number of users x resource hours x number of dates?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hello @parry2k 

 

In the perfect world, the resources should enter their hours in the Timesheet every day. But that doesn't always happen, sometimes the resource forgets doing the time tracking.

 

What I would like to have is a control of how many hours should have been entered, and how many hours were actually entered. So I can see the % of adherence to the system ... as well as the amount of overtime.

 

Basically: if I have 3 resources, in 1 business week they should work 8 hours per day. That is 3x8x5 = 120 hours

 

However, the catch is that some resources work 8 hours a day, while other resources work 6 hours. That's why I have the TabResource

Hi @Anonymous ,

First, you can create one calendar table if it still not be created. Then create one measure as below to calculate the hours per date:

Hours per day = 
SUM ( 'TabResources'[Hours per day] )
    * CALCULATE (
        COUNTROWS ( 'Calendar' ),
        DATESBETWEEN (
            'Calendar'[Date],
            MIN ( 'Timesheet'[Date] ),
            MAX ( 'Timesheet'[Date] )
        ),
        WEEKDAY ( 'Calendar'[Date], 2 ) < 6
    )

help with dates.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello, @v-yiruan-msft 

 

The problem is that this Hours per Day is showing me the sum of all Resources. The ideal would be to have the filter when you have a resource selected.

 

Or am I doing something wrong?

@Anonymous ok if that is the business logic, it can be achieved, assuming everybody worked everyday, if only one user has entered the data , we will assume total resource x there hours per day x number of days 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous solution is attached, you can take it from here.

 

Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

The same happens with your solution @parry2k 

@Anonymous not sure what you mean by same happened with the solution, You have to be more specific and provide as much as detail as possible.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Sorry, let me try again

 

Sem título.jpg

 

The total number of Resources that are using the Time Tracking System is 61. Some work 6 hours, others 8 hours. If everyone works their respective hours in a day, the sum should be 484.

 

In the attached photo, I selected 3 resources (that work 8 hours a day). Thar card should show 24 hours, not 484.

BUT, while writing this message, I went to check something in the BI and saw that the filters were using another data source. So the cards weren't being filtered ... totally my fault 😅

 

Thank you very much for your help @parry2k 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.