Creating Week Range With Leading 0's

[bernate]

Hello, I am trying to create a week range column in my date table. I got the start and end date columns in Power Query, changed their format to mm/dd/yyyy in Table view, and concatenated them but the leading 0's didn't stay.

I want the Week Range to show 01/02/2024-01/07/2024 so that when the column is sorted in a visual the week ranges are in the correct order by date. I also tried to sort the Week Range column by a date column and that didn't work because I have repeating values in the Week Range column. 

[v-xiandat-msft]

Hi @bernate ,

Below is my table:

The following DAX might work for you:

Week Range = COMBINEVALUES("-",FORMAT('Table'[StartOfWeek],"mm/dd/yyyy"),FORMAT('Table'[EndOfWeek],"mm/dd/yyyy"))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[Ashish_Mathur · Accepted solution]

Hi,

If you are OK with a DAX solution, then write this calculated column formula

Week range = format(Data[Start of week],"mm/dd/yyyy")&"-"&format(Data[End of week],"mm/dd/yyyy")

Hope this helps.

[bernate]

Worked perfectly, thank you for your help!

[Daniel29195]

hello @bernate 

for leading zero, can you please change mm to MM. 

for the ordering ,

i would suggest to create a start of week column, and sort the week range by this column. 

let me know if this would help you . 

best regards