Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

WEEKNUM in date table, SAMEPERIODLASTYEAR giving the wrong answer (off by by a few)

I am trying to find the net difference between previous years data based on a period using a slicer.  The slicer uses the WEEKNUM column from my Calendar table.  I have a measure that gives me a rate from my Data table. I created another measure to give me the rate for the previous year using:

 

PrevRate = CALCULATE([CurRate], SAMEPERIODLASTYEAR('calendar'[Date])

 

In the visual I am getting weird results

 

Weeks 1 to 39:

 

YearCurRatePrevRate
2017312.16 
2018295.82314
2019 298

 

Weeks  5 to 36:

 

YearCurRatePrevRate
2017293.99 
2018274.58294
2019 282

 

It's weird because the rates are sometimes very similar, but can be off by a lot as well. When I change the slicer to use the Month column the results match exactly.

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

Hi jgoddard,

 

Are the date continuous? You may try PREVIOUSYEAR instead if the date value is not continuous. If this issue persists, please share some sample data or share some screenshot of your raw data and do mask the sensitive data.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

The dates are continuous, however not necessarily YTD.  Some times we need to compare from Week 21, and sometimes from Week 16 to current.

Anonymous
Not applicable

It also does not sum properly when using the Date column (range is changed daily).

Hi jgoddard,

 

Could you please share some sample data?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.