cancel
Showing results for
Did you mean:
Post Patron

## Calculate Year on Year using Year and week number

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User II

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:

Hope this helps,

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

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

Happy to help!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.