Forum Discussion
Creating Week Range With Leading 0's
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.
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.
4 Replies
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
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.
Worked perfectly, thank you for your help!
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.
