Multiple rows based on dates and week number

[isaideepika]

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

[v-xuding-msft · Accepted solution]

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

[isaideepika]

HI @v-xuding-msft 

Thanks 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.