Week Number to Date range

[prabhuk]

Hello,

Is there any solution to have date range for week number in calender table. Example Week 1 represent first 5 days of the month than, date range column show 01-05.

I have attached sample BPI file for you reference. Kindly help solve this.

https://www.dropbox.com/s/i1tgbulqeq00nl7/PBI%20File.pbix?dl=0 

Prabhu

[v-alq-msft · Accepted solution]

Hi, @prabhuk 

I'd like to suggest you create a calculated column as below. The pbix file is attached in the end.

Year-Month-Week-Day = 
var year = YEAR([Date])
var month = FORMAT([Date],"mmm")
var weeknum = WEEKNUM([Date],2)
var _min = 
MINX(
    FILTER(
        CALENDAR(MIN('Time'[Date]),MAX('Time'[Date])),
        YEAR([Date])=year&&
        FORMAT([Date],"mmm")=month&&
        WEEKNUM([Date],2)=weeknum
    ),
    [Date]
)
var _max = 
MAXX(
    FILTER(
        CALENDAR(MIN('Time'[Date]),MAX('Time'[Date])),
        YEAR([Date])=year&&
        FORMAT([Date],"mmm")=month&&
        WEEKNUM([Date],2)=weeknum
    ),
    [Date]
)
return
year&"-"&month&"-"&"WeekNum:"&weeknum&"-"&DAY(_min)&"-"&DAY(_max)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.