cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
igibson Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Last Year Sales Data is Off By One Week

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

Re: Last Year Sales Data is Off By One Week

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

4 REPLIES 4
Super User
Super User

Re: Last Year Sales Data is Off By One Week

Hi,

 

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

Community Support Team
Community Support Team

Re: Last Year Sales Data is Off By One Week

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.
Community Support Team
Community Support Team

Re: Last Year Sales Data is Off By One Week

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

Re: Last Year Sales Data is Off By One Week

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