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
igibson
Frequent Visitor

Last Year Sales Data is Off By One Week

I have a matrix to compare the sales of the past week (week 37) and the same week last year as such: 

PY_SalesAmt = CALCULATE(sum(Sales[NetAmount]),DC03_Calendar[Fin_Year] = "2017")

The problem I am encountering is that instead of pulling the data from last year week 37 it is pulling the data from last year week 38.

 

How can I make an adjustment so these dates line up properly? My last fiscal year is 53 weeks which i believe is the problem.

2 ACCEPTED SOLUTIONS
v-jiascu-msft
Employee
Employee

Hi @igibson,

 

According to my test, DATAADD and SAMEPERIODLASTYEAR both have a granularity of DAY. Maybe we have to get the same week by ourselves if you want the exact same week (same week number). 

CouldbeRight =
VAR currentWeeknum =
    MIN ( 'Date'[Weeknum] )
VAR currentYear =
    YEAR ( MIN ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        FIRSTDATE ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Weeknum] = currentWeeknum
                && YEAR ( 'Date'[Date] )
                    = currentYear - 1
        )
    )

Last Year Sales Data is Off By One Week .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As we can see from the picture, the first day of week 52 of 2010 is 12/19/2010, not 12/18/2010.

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

igibson
Frequent Visitor

A very simple soultion that I implemented was a change in the calendar. I simply used Fin_Week = WEEKNUM(DC03_Calendar[Date]+1,21).

View solution in original post

4 REPLIES 4
igibson
Frequent Visitor

A very simple soultion that I implemented was a change in the calendar. I simply used Fin_Week = WEEKNUM(DC03_Calendar[Date]+1,21).

v-jiascu-msft
Employee
Employee

Hi @igibson,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

Hi @igibson,

 

According to my test, DATAADD and SAMEPERIODLASTYEAR both have a granularity of DAY. Maybe we have to get the same week by ourselves if you want the exact same week (same week number). 

CouldbeRight =
VAR currentWeeknum =
    MIN ( 'Date'[Weeknum] )
VAR currentYear =
    YEAR ( MIN ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        FIRSTDATE ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Weeknum] = currentWeeknum
                && YEAR ( 'Date'[Date] )
                    = currentYear - 1
        )
    )

Last Year Sales Data is Off By One Week .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As we can see from the picture, the first day of week 52 of 2010 is 12/19/2010, not 12/18/2010.

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

If in your filter, you have chosen a specific date range, then use the SAMEPERIODLASTYEAR() function in the CALCULATE function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.