Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi community,
I am new to Power BI, and I have a question about calculating timesheet hours. I have a timesheet table (sample) as follow:
Project | Resource | Resource Type | Timesheet Start | Timesheet End | Timesheet Month | Hours | Timesheet Status | Timesheet Creation | Forecast Hours |
A | John | Employee | 9/23/2018 | 9/29/2018 | 9 | 8 | Frozen | 9/25/2018 | 20.00 |
A | John | Employee | 9/23/2018 | 9/29/2018 | 9 | 16 | Frozen | 9/24/2018 | 20.00 |
B | John | Employee | 9/16/2018 | 9/22/2018 | 9 | 10 | Pending | 9/17/2018 | 30.00 |
B | John | Employee | 9/16/2018 | 9/22/2018 | 9 | 10 | Frozen | 9/18/2018 | 30.00 |
There are two types of status: frozen and pending. I need to create 2 calculations in the table: Submitted Hours (only include pending hours in a week) and Frozen Hours (aggregate hours for all the previous weeks within curren timesheet month, and less than timesheet creation date). The expected result will be as follow:
Project | Resource | Resource Type | Timesheet Start | Timesheet End | Timesheet Month | Hours | Timesheet Status | Timesheet Creation | Forecast Hours | Frozen Hours | Submitted Hours |
A | John | Employee | 9/23/2018 | 9/29/2018 | 9 | 8 | Frozen | 9/25/2018 | 20.00 | 34 | 0 |
A | John | Employee | 9/23/2018 | 9/29/2018 | 9 | 16 | Frozen | 9/24/2018 | 20.00 | 26 | 0 |
B | John | Employee | 9/16/2018 | 9/22/2018 | 9 | 10 | Pending | 9/17/2018 | 30.00 | 10 | 10 |
B | John | Employee | 9/16/2018 | 9/22/2018 | 9 | 10 | Frozen | 9/18/2018 | 30.00 | 10 | 0 |
Also, for the card visuals, I want to have 2 values: total of forecast hours per project per resource per timesheet month, and total of frozen hours per resource per month, so in this case, Forecast Hours (card visual) will be 50, and Frozen Hours will be 34. This part is optional, so in the meantime I am looking for Frozen hours and Submitted Hours calculation only.
I would really appreciate for your answers!
Solved! Go to Solution.
Hi @nathan115,
Please create two calculated columns like below.
Submitted Hours = IF ( TimeSheet[Timesheet Status] = "Pending", CALCULATE ( SUM ( TimeSheet[Hours] ), FILTER ( ALLEXCEPT ( TimeSheet, TimeSheet[Timesheet Month], TimeSheet[Timesheet Start], TimeSheet[Timesheet End] ), TimeSheet[Timesheet Status] = "Pending" ) ), 0 ) Frozen Hours = CALCULATE ( SUM ( TimeSheet[Hours] ), FILTER ( ALLEXCEPT ( TimeSheet, TimeSheet[Timesheet Month] ), TimeSheet[Timesheet Creation] <= EARLIER ( TimeSheet[Timesheet Creation] ) && TimeSheet[Timesheet Status] = "Frozen" ) ) + 0
For the two values displayed in card visual, please refer to these measures:
total of forecast hours = VAR temptable = SUMMARIZE ( TimeSheet, TimeSheet[Project], TimeSheet[Resource], TimeSheet[Timesheet Month], TimeSheet[Timesheet Start], TimeSheet[Timesheet End], "Total Forecast", FIRSTNONBLANK ( TimeSheet[Forecast Hours], 1 ) ) RETURN SUMX ( temptable, [Total Forecast] ) total of frozen hours = CALCULATE ( MAX ( TimeSheet[Frozen Hours] ), ALLEXCEPT ( TimeSheet, TimeSheet[Project], TimeSheet[Resource], TimeSheet[Timesheet Month] ) )
Best regards,
Yuliana Gu
I am trying to create the frozen hour as a measure to make it more dynamic, but I got an error saying "EARLIER refers to an earlier row context which doesn't exit". Do you know how I can modify it to make the frozen hour a measure instead?
Hi @nathan115,
Please create two calculated columns like below.
Submitted Hours = IF ( TimeSheet[Timesheet Status] = "Pending", CALCULATE ( SUM ( TimeSheet[Hours] ), FILTER ( ALLEXCEPT ( TimeSheet, TimeSheet[Timesheet Month], TimeSheet[Timesheet Start], TimeSheet[Timesheet End] ), TimeSheet[Timesheet Status] = "Pending" ) ), 0 ) Frozen Hours = CALCULATE ( SUM ( TimeSheet[Hours] ), FILTER ( ALLEXCEPT ( TimeSheet, TimeSheet[Timesheet Month] ), TimeSheet[Timesheet Creation] <= EARLIER ( TimeSheet[Timesheet Creation] ) && TimeSheet[Timesheet Status] = "Frozen" ) ) + 0
For the two values displayed in card visual, please refer to these measures:
total of forecast hours = VAR temptable = SUMMARIZE ( TimeSheet, TimeSheet[Project], TimeSheet[Resource], TimeSheet[Timesheet Month], TimeSheet[Timesheet Start], TimeSheet[Timesheet End], "Total Forecast", FIRSTNONBLANK ( TimeSheet[Forecast Hours], 1 ) ) RETURN SUMX ( temptable, [Total Forecast] ) total of frozen hours = CALCULATE ( MAX ( TimeSheet[Frozen Hours] ), ALLEXCEPT ( TimeSheet, TimeSheet[Project], TimeSheet[Resource], TimeSheet[Timesheet Month] ) )
Best regards,
Yuliana Gu
Thank you Yuliana! I have to modify couple things here and there, but other than that, the formula works like a charm!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |