Forum Discussion
Dynamic Measure based on selected filters/slicers
Yes, the filter is from a field on the table Performance
At the moment there's a date table sitting as the bridge between Performance and LY Performance and that's where the measure is
Performance is mapped to TYWeek and LY Performance mapped to LYWeek
The relationshsips are both 'Both'. The filter is in the table Performance
- vanessafvg
Community Champion
ok just a few thoughts here
-worker is being applied to performance but not last years performance because there is no link there the link is on date not worker.
Is the structure and granularity of ly data the same as this years data? if so why not combine it?
basically in order to filter both performance and last years performance to worker you need a relationship there. because you already have a relationship on the date on date it might be better to combine the two tables and then do a calculation in dax to get py performance
Well that opens the can of worms that is how I'm dealing with this YoY comparison....
I couldn't work out how to do it properly in DAX so I did an ugly fudge of basically duplicating the Performance table and linking the TY week field to the LY week field.
If you could advise on the 'correct' way to compare Week 201617-01 to Week 201718-01 then I'd be very happy. I think a problem is that the data is summarised at a weekly level rather than daily? Like I said: I'm simple and new to this!
- vanessafvg
I think something like this might work better
https://blog.crossjoin.co.uk/2012/06/20/comparing-any-two-time-periods-in-dax/
i think there are a variety of ways to deal with what you trying to do, but this seems fairly simple to understand and implement.
This is also quite a long (and not completely relevant to your scenario) but helpful in understanding how time , date works with DAX
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
- vanessafvg
could 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
