Continuous value from week number and day of week

[mateoc15]

I have a line chart and I want to see two lines, one for current year and one for previous year, but I want to do so by the week number and day of week because Saturdays and Sundays always have lower numbers so I want to compare, for instance, the first Saturday 2024 to first Saturday of 2023, rather than Jan 1 2024 to Jan 1 2023.  I have that figured out.  My X axis is discrete values though - first the week number, then the day of week.  Because of this there's a horizontal scroll bar and I want to get rid of it.  Is there any way to make such a YoY day of week a continuous thing?  I want to see all 53 weeks at once without scrolling.

[parry2k]

@mateoc15 Yeah, not sure why it wouldn't work. It works with the format function but that will defeat the purpose because it will change the date to a text and you will not get the continuous option.

I guess report it as an issue, I don't have any more suggestions for you. 

[mateoc15]

I could have sworn I saw "ww" or "WW" as a date format string, but maybe not? https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings When I enter "ww ddd" or "WW ddd" it just comes out as "Mon", like ddd only.

Here's a hack I don't really like, but it's continuous - Tuesday, Jan 1 would be "1.3" - week one, third day of the week (Sun-Sat).  It's not really readable though.

WEEKNUM([Date]) + (WEEKDAY([Date])/10

[v-rzhou-msft]

Hi @mateoc15 ,

As far as I know, if you want to use continuous data in the visual x-axis, the best way to do it is to use columns of numeric or date type, not text type.

I don't think “ww” works in the format, only year/quarter/month/day in time intelligence.” ww” doesn't make sense.

Your alternative is actually correct, you could try using.

WW DD = WEEKNUM([Date])*100 + (WEEKDAY([Date])

That might be a bit more intuitive.

Best Regards,
Rico Zhou

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

[mateoc15]

Thanks so much, definitely the right direction.  I'll tinker with the formatting.  In the meantime I've created a week number/day of week hierarchy and at least I can drill up/down.

[parry2k]

@mateoc15 @Sounds good 

[parry2k]

@mateoc15 try this, I think it will work, make a copy of the date column and call is "day name" or whatever name you want, and in the format string add the following, and use this column instead of the current column you are using for the day name.

[mateoc15]

I think this is moving in the right direction.  I want the week as well as day of week, so I tried "ww ddd" but it doesn't seem to work.  Reference https://learn.microsoft.com/en-us/dax/format-function-dax#custom-datetime-formats