Forum Discussion

isaideepika's avatar
isaideepika
Regular Visitor
7 years ago
Solved

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 ...
Tips and Tricks
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    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

v-xuding-msft's avatar
v-xuding-msft
Community Support
7 years ago

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's avatar
isaideepika
Regular Visitor
7 years ago

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.