Weeks in Current Year Compared to Weeks in Previous Year

[StephenK]

Hello all,

I'm attempting to build an analysis that compares the number of patient encounters by week for a set period this year (ie. last six calendar weeks) compared to the number of patient encounters by week for the same period last year.

I have not found a good solution as of yet. My data model consists of a Date Dim that includes calculated columns for Week Number in Year as well as a concatenated WeekNumber and Year column. My date dim is connected by a one to many relationship on the Date column to my fact table which also includes a date column.

I've tried creating a custom date hierarchy consisting of Year>Month>WeekNumber>Date. 

I then visualize this with a bar chart with the hierarchy on the axis and 2 measures in the values.

Measure 1 is a sum of all patient encounters: COUNT([encounterid]) 

Measure 2 is supposed to be the concurrent weeks for the previous year. I had attempted to do this by 

CALCULATE(COUNT([encounterid]),SAMEPERIODLASTYEAR('Date Dim',[Date]).

The report has a relative date slicer based on the date field in date dim.

My goal was to be able to analyze the prior year weeks compared to any year selected, hence the reason Measure 1 has no calculate/filter.

On the surface everything appears to work (the numbers after the month name are actually week numbers, not days), but the numbers start to go wonky depending on the period you're looking at. Example last six calendar weeks looks okay, but last eight calendar weeks starts to look weird...

Additionally, when i go into my dataview and do some spot checks on my weeks, i have dates disappearing and not showing up in that week for unapparent reasons--typically just one or two days here or there.

I've scoured the forums here for a solution and haven't found something that works. 

I feel like I'm missing an obvious solution with this, but I've been beating my head against the wall on this for too long now.

Any help would be greatly appreciated!

Thanks

[amitchandak · Accepted solution]

Week of last year is 364 days behind. So if you create a trailing measure with 364 days behind with date calendar you will get. Also, refer to my file where I have used Rank for this week vs last week. In the case of Rank, Last year is 52 weeks behind.

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

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/

If you need more help mark me @

Appreciate your Kudos.