datetimepowerbibiweekly

Weekly Period Column For Each Month in Power BI


I have a date table with a date column. I would like to have a weekly period for each month. So far, I have built a custom column such that each period starts on Monday and ends on Sunday:

let         
startOfWeek = Date.StartOfWeek([Date]),
endOfWeek = Date.EndOfWeek([Date]),
yearOfStartOfWeek = Date.Year(startOfWeek),
yearOfEndOfWeek = Date.Year(endOfWeek),
    valueToReturn = if yearOfStartOfWeek = yearOfEndOfWeek then
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek([Date]))
else
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek(endOfWeek))
in valueToReturn

This will give me the custom column "Weekly Period" below: enter image description here

But what I need to have is this way of weekly period:


Solution

  • For those that might be helpful, here I found the answer for Weekly Period:

    let         
    startOfWeek = Date.StartOfWeek([Date]),
    endOfWeek = Date.EndOfWeek([Date]),
    weekOfStartOfWeek = Date.WeekOfYear(startOfWeek),
    weekOfEndOfWeek = Date.WeekOfYear(endOfWeek),
    
    valueToReturn = if  (Date.Month(Date.StartOfWeek([Date])) = Date.Month([Date]) and Date.Month(Date.EndOfWeek([Date])) = Date.Month([Date]))
    then 
    Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfWeek([Date])) 
    
    else if  Date.Month(Date.StartOfWeek([Date])) <> Date.Month([Date]) then
        Text.From([Date.StartOfMonth([Date])]) & " - " & Text.From(Date.EndOfWeek(endOfWeek))
    
       else
        Text.From(Date.StartOfWeek([Date])) & " - " & Text.From(Date.EndOfMonth([Date]))
    
    in
    valueToReturn