Forum Discussion
Fill missing weeks and calculate hours per week
Hi!
I have a dataset with accumulated values per random weeks.
I want to calculate hours per week. In the cases where a consecutive week is missing I would like to add the week and a zero value for each id.
| Id | yyww | acc hours |
| a | 2208 | 110 |
| a | 2207 | 106 |
| a | 2203 | 85 |
| a | 2201 | 56 |
| a | 2151 | 36 |
| a | 2150 | 15 |
| a | 2148 | 10 |
| a | 2146 | 5 |
| b | 2208 | 164 |
| b | 2207 | 152 |
| b | 2206 | 148 |
| b | 2205 | 108 |
| b | 2204 | 57 |
| b | 2203 | 56 |
| b | 2202 | 22 |
| c | 2205 | 69 |
| c | 2201 | 15 |
| c | 2110 | 14 |
| c | 2109 | 8 |
| c | 2105 | 6 |
| c | 2104 | 4 |
| c | 2101 | 2 |
Any ideas?
/Anna.
Hi,
I suggest creating a calendar table like the attached pbix file, and create a measure like below.
Calendar Table:
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2022, 12, 31 ) ), "YYYYWW", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date], 21 ) )Measure:
Hours: = VAR currentweeknumber = MAX ( 'Calendar'[YYYYWW] ) VAR acc_hours = CALCULATE ( SUM ( Data[acc hours] ), FILTER ( Data, Data[yyww] + 200000 = currentweeknumber ) ) VAR previousweeknumber = MAXX ( FILTER ( Data, Data[yyww] + 200000 < currentweeknumber ), Data[yyww] ) + 200000 VAR previousacchours = CALCULATE ( SUM ( Data[acc hours] ), FILTER ( Data, Data[yyww] + 200000 = previousweeknumber ) ) RETURN IF ( acc_hours <> 0, acc_hours - previousacchours, 0 )
3 Replies
- Jihwan_Kim
Super User
Hi,
I suggest creating a calendar table like the attached pbix file, and create a measure like below.
Calendar Table:
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2022, 12, 31 ) ), "YYYYWW", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date], 21 ) )Measure:
Hours: = VAR currentweeknumber = MAX ( 'Calendar'[YYYYWW] ) VAR acc_hours = CALCULATE ( SUM ( Data[acc hours] ), FILTER ( Data, Data[yyww] + 200000 = currentweeknumber ) ) VAR previousweeknumber = MAXX ( FILTER ( Data, Data[yyww] + 200000 < currentweeknumber ), Data[yyww] ) + 200000 VAR previousacchours = CALCULATE ( SUM ( Data[acc hours] ), FILTER ( Data, Data[yyww] + 200000 = previousweeknumber ) ) RETURN IF ( acc_hours <> 0, acc_hours - previousacchours, 0 )Hi! This certainly seems to work for you but I can't get the total per week right. I think I have managed using "Earlier" and a group index I found here https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
Samarth_18Community Champion
Hi Anonymous ,
Please use date table for week calculation and return zero for blank values.
Thanks,
Samarth