Calculate Year on Year using Year and week number

[bo_afk]

I would like to calculate the year on year difference for one of my metrics using year and week number.

I know that the SAMEPERIODLASTYEAR function can used for this but my dataset doesnt contain dates per se, so unfortunately I can't use this.

Sample data below

Year	Week Number	Sales
2018	1	450
2018	2	300
2018	3	475
2018	….
2018	52	270
2019	1	300
2019	2	210
2019	3	370
2019	4	480

Selected date:

2019 week 2 - YoY difference is -90 (210-300)

2019 week 3 - YoY difference is -105 (370-475)

I would like this to be dynamic so that when i select a week from 2019, it will automatically calculate the difference between the same week in 2018.

Thanks!

afk

[v-juanli-msft · Accepted solution]

Hi @bo_afk 

Create two tables

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],2))

filter date table(not connected) = 'date'

Create two measures

Measure =
IF (
    MAX ( 'date'[year] )
        = SELECTEDVALUE ( 'filter date table(not connected)'[year] )
        && MAX ( 'date'[week] )
            = SELECTEDVALUE ( 'filter date table(not connected)'[week] ),
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[date] )
                = SELECTEDVALUE ( 'filter date table(not connected)'[year] ) - 1
                && WEEKNUM ( 'Table'[date], 2 )
                    = SELECTEDVALUE ( 'filter date table(not connected)'[week] )
        )
    )
)

Measure 2 = IF([Measure]<>BLANK(),SUM('Table'[sales])-[Measure])

Best Regards
Maggie

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

[ibarrau]

Hi. I think the best way is adding a Date Table and relate it with your data. Then you can make time intelligence like sameperiodlastyear function.

You can created from scratch. This like have three examples:

https://blog.ladataweb.com.ar/post/185012464051/data-modeling-como-hago-una-tabla-fecha

Hope this helps,

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