Forum Discussion
Dynamic Measure based on selected filters/slicers
Tried to replicate what's done in the first link but getting nowhere fast! I think part of the problem is my date table looks like this so therefore can't behave like a date table. The calculation I'm trying to do is maddening simple: take the total for TYWeek and compare that to the total for LYWeek (whilst paying attention to the filters applied)
| Year Number | Week | Year | LY | TY | TYWeek | LYWeek |
| 201516 | 1 | 2015/16 | 201415 | 201516 | 2015161 | 2014151 |
| 201516 | 2 | 2015/16 | 201415 | 201516 | 2015162 | 2014152 |
| 201516 | 3 | 2015/16 | 201415 | 201516 | 2015163 | 2014153 |
| 201516 | 4 | 2015/16 | 201415 | 201516 | 2015164 | 2014154 |
I think I'm going to have to take a break from this
Community Championcould you not create a date table, and then assign the first or last date of the week to your week numbers? Then it would be able to rollup easily.
OK, I've added an extra column to the date table populated with the first day of that week but I don't see how this helps me.
I'm just getting frustrated now so am tempted to nuke the whole report and start from scratch (and maybe learn what I'm doing first)
- vanessafvg
id go with option 2, sometimes you just have to start from scratch.
1. create a date table
2. link that date table to your transactional data
this is definitely best practise and make sure your data types are set correctly, this gives you access to a multitude of data functions.
other dax functions you can use for the previous year values
paralellperiod https://msdn.microsoft.com/en-us/library/ee634873.aspx
sameperiodlastyear https://msdn.microsoft.com/en-us/library/ee634972.aspx
Started with a rebuild but everything's the same :/
I can't work out how to get it to look week numbers year-to-year rather dates i.e.; week 08 in 2016/17 doesn't have the same dates as week 08 in 2015/16
PARALLELPERIOD comes close, but will only let me look at years/quarters/months