Forum Discussion
Continuous week number with year
Hello everyone,
I am new to PowerBI and still learning. I have 1 year worth of data which runs from March 2019 to March 2020 which I need to get a comparison in volume per week.
I have this formula to get the week number :
Reported Week = WEEKNUM('Reported Date'[Reported Date])
The issue is when I am putting it into a visual, it puts the january 2020 week at the beginning of the visual.
I tried this formula :
Reported Week Year = YEAR('Reported Date'[Reported Date]) & "W" & WEEKNUM('Reported Date'[Reported Date])
But X axis type is only at categorical which makes the highest value of the week at the beginning of the visual and lowest at the end.
Any suggestions? Thank you so much!
Anonymous
try
Reported Week Year = var _W = WEEKNUM('Reported Date'[Reported Date]) RETURN YEAR('Reported Date'[Reported Date]) & "W" & IF(_W<10, "0"&_W, _W)
4 Replies
- az38
Community Champion
Hi Anonymous
your statement should work correct if you place it into axis.
try to yse Sort by column button as described here https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
- az38
Anonymous
try
Reported Week Year = var _W = WEEKNUM('Reported Date'[Reported Date]) RETURN YEAR('Reported Date'[Reported Date]) & "W" & IF(_W<10, "0"&_W, _W)
- FrankAT
Hi,
- You have to have a calendar table related to your fact table.
- This calendar table should be bounded by the smallest and largest date in the fact table.
- Use the weeknum from this bounded calendar table.
Regards FrankAT
