Dynamic Measure based on selected filters/slicers

[bytheway]

This feels like an incredibly simple problem, but I'm an incredibly simple person.

I'm doing a revenue chart with one line for each year. I have a measure which compares the YoY growth ebtween years. The measure behaves when I have no slicers engaged but falls apart the second I select something in the slicer. So my question is: how do you have the measure respond to the selected slicers? I can see what the overall growth is for the whole business, but when I've got a slicer on WORKER A, I want the measure to just return the overall growth for WORKER A. The measure will be used as a tooltip on a linegraph.

Thanks for your help!

[vanessafvg]

can you post the dax code here?  

when you say fall apart, what happens exactly

[bytheway]

Sum of last year:

LY All Rev = SUM('LY Performance'[Revenue])

Sum of this year:

TY All Rev = SUM(Performance[Revenue])

YoY Growth:

YoY Growth All Rev = ([TY All Rev]/[LY All Rev])-1

When used as a tooltip it displays the correct % value week to week, but when any slicers are engaged the % value drops dramatically which suggests it's comparing the smaller filtered value to the grand total for the previous year. They don't tally with the quoted Revenue figures used in the calculation

[vanessafvg]

firstly i would do a divide in dax as 

YoY Growth All Rev =  divide([TY All Rev],[LY All Rev])

(not sure it will make a diff but worth a try)

what is the -1 for?

[bytheway]

I've updated it to:

YoY Growth All Rev = DIVIDE([TY All Rev],[LY All Rev],0)-1

and it behaves the same as before

The -1 is to give me the true growth figure (e.g.; if LY=50 and TY=100 I want that to show the growth as 100% rather than the 200% it would show without the -1

[vanessafvg]

and your filter, is it all in the same table?

[bytheway]

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

[bytheway]

Performance is mapped to TYWeek and LY Performance mapped to LYWeek

The relationshsips are both 'Both'. The filter is in the table Performance

[vanessafvg]

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

[bytheway]

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

http://www.daxpatterns.com/time-patterns/

[bytheway]

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.

[bytheway]

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

[bytheway]

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

[vanessafvg]

right ok thats make more sense now, and the filter which table is it sitting in?

is the direction of the relationship single or both?

[vanessafvg]

can you do a few screen shots maybe?  and also of your relationships if its more than one table