Forum Discussion

Post Patron

6 years ago

Solved

Comparison vs same week last year

I'm trying to compare the sales from this week this year, vs the same week last year (based on week number). Whilst I do have dates in my data, I can't use the SAMEPERIODLASTYEAR function since th...

Need Help

bo_afk
6 years ago
All, I finally managed to find a solution here in this link https://forum.enterprisedna.co/t/same-period-last-year-for-fiscal-week-nbr-weeks-in-sales-table-are-non-standard/240/8

What I had to do was to create a measure based on the measure that you all had suggested.

v-alq-msft

Community Support

6 years ago

Hi, bo_afk

Based your description, I created data to reproduce your scenario.

DateTable(a calculated table):

DateTable = 
CALENDAR(DATE(2019,1,1),DATE(2020,12,12))

Sales:

There is a one-to-one relationship between two tables.

Then you may create measures as follows.

Sales this year-week = 
var _date = MAX(Sales[Date])
var _year = YEAR(_date)
var _week = WEEKNUM(_date)
return
IF(
    ISBLANK(MAX(Sales[Date])),
    BLANK(),
    CALCULATE(
        SUM(Sales[Sales]),
        FILTER(
            ALLSELECTED(Sales),
            YEAR(Sales[Date]) = _year&&
            WEEKNUM(Sales[Date]) = _week
        )
    )
)

Sales last year-week = 
var _date = MAX(Sales[Date])
var _lastyear = YEAR(_date)-1
var _lastweek = WEEKNUM(_date)

return

    CALCULATE(
        SUM(Sales[Sales]),
        FILTER(
            ALL(Sales),
            YEAR(Sales[Date]) = _lastyear&&
            WEEKNUM(Sales[Date]) = _lastweek
        )
    )

Year-Week change = 
if(
    ISBLANK(Sales[Sales last year-week]),
    BLANK(),
    Sales[Sales this year-week] - Sales[Sales last year-week]
)

Result: