cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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
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

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

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors