Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |