Week number that factors in year

[Anonymous]

Hello! I have a graph that shows total number of intake items by week number: Intake Date # = WEEKNUM([releasedate], 1). We use this for YOY comparisons. This works fine for most of the year, but starts to break down towards the end of the year when intake weeks from 2019 and 2020 overlap. How could I create a smarter measure that can recognize both the week number of a date as well as the year?

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understand, you want to calculate the total Number based on the same weekNo but different years ,right?

You may need to create columns for weekNo and Year separately

weekNo =
WEEKNUM ( [releasedate], 1 )

year =
YEAR ( [releasedate] )

 And then set year as legend ,weekNo as X-axis like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Greg_Deckler]

@Anonymous - Another thought on this:

YearWeek = YEAR([Date]) * 100 + WEEKNUM([Date])

That should produce results like:

202001

202002

202003

...

202101

202102

202103

etc. Not sequential but at least unique to each year. 

[amitchandak]

@Anonymous , how are doing YOY comparison, Like if you use Datesytd, it will take only data for that year. But week, not group properly . Prefer to use date calendar for that

For that, you need to change the boundaries of first and last week

first, get a week start date

Week Start date = 'Date'[releasedate]+-1*WEEKDAY('Date'[releasedate],1)+1

Week Year = if([Week] = 53 && year([Week Start date] <> [releasedate],1,53)

This will add an additional day to week one and this you can use to get week sum up correctly

YTD example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Week table -https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

@Anonymous - Well, you could concatenate them together I suppose, like [Year] & [WeekNum] or you could use Sequential which I sort of built for this purpose. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116