cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo_afk
Post Patron
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

YearWeek NumberSales
20181450
20182300
20183475
2018…. 
201852270
20191300
20192210
20193370
20194480

 

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
v-juanli-msft
Community Support
Community Support

Hi @bo_afk 

Create two tables

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

filter date table(not connected) = 'date'

Capture17.JPG

 

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

Capture16.JPG

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @bo_afk 

Create two tables

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

filter date table(not connected) = 'date'

Capture17.JPG

 

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

Capture16.JPG

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.

View solution in original post

ibarrau
Super User II
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:

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.


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

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors