Forum Discussion
Multiple rows based on dates and week number
Hi,
I need a solution for the below issue in powerbi. Based on start date and end date and total hours as in Table 1, I want to get the output - dates(start date-friday of that week), weeknumber and hours split per each week (@8hrs. per day)
Example-
I have the following data-
Start date End date Total hours
5/8/2019 29/8/2019 144
OUTPUT SHOULD BE-
Week number Total Hours
5/8/2019 9/8/2019 32 40
12/8/2019 16/8/2019 33 32
19/8/2019 23/8/2019 34 40
26/8/2019 29/8/2019 35 32
Please help.
Thanks in advance.
-Deepika
Hi isaideepika ,
I created a sample using calculted column that you can reference and download.
table = CALENDAR(DATE(2019,8,5),DATE(2019,8,29)) WeekNum = WEEKNUM('table'[Start date]) WeekDay = WEEKDAY('table'[Start date],2) End date = IF('table'[WeekDay] = 1,IF(DATEADD('table'[Start date],4,DAY) = BLANK(),MAX('table'[Start date]),DATEADD('table'[Start date],4,DAY))) Total Hours = CALCULATE(COUNT('table'[Start date]),FILTER(ALLEXCEPT('table','table'[WeekNum]),'table'[WeekDay]>0 && 'table'[WeekDay]<6)) * 8
2 Replies
Hi isaideepika ,
I created a sample using calculted column that you can reference and download.
table = CALENDAR(DATE(2019,8,5),DATE(2019,8,29)) WeekNum = WEEKNUM('table'[Start date]) WeekDay = WEEKDAY('table'[Start date],2) End date = IF('table'[WeekDay] = 1,IF(DATEADD('table'[Start date],4,DAY) = BLANK(),MAX('table'[Start date]),DATEADD('table'[Start date],4,DAY))) Total Hours = CALCULATE(COUNT('table'[Start date]),FILTER(ALLEXCEPT('table','table'[WeekNum]),'table'[WeekDay]>0 && 'table'[WeekDay]<6)) * 8Thanks a lot for the solution but my input data overview is something like below. So based on this input, i need the output(in data) as you have given. The output should be further merged with another table(which i will do later) after i get the intended output as mentioned previously.
