Last Year Sales Data is Off By One Week

[igibson]

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.

[v-jiascu-msft · Accepted solution]

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

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

[igibson · Accepted solution]

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]

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

[v-jiascu-msft · Accepted solution]

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

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

[Ashish_Mathur]

Hi,

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