Team Performance vs. Target - with BOOKMARKS and TOGGLE

Hi Ongzph,

You'll need to do a little bit of work behind the scenes to do this, including writing a few dax formulas. First up you need to create a new 2-column table:

No     Date Option

1        YTD

2        Past 30 Days

3        Past 90 Days

4         .... Keep going with all the time options you want.

Also create a new measure: Selected Timeframe:=min(Table[No]). (Eventually you will show the 'Date Option' on the chiclet slicer, and this measure will harvest your selection. Do not create any connections for this table.

Next up, in your main data table, you will need to write a new dax formula for each of the date options. You should be able to use time intelligence functions to help achieve some of these. E.g.

Sum YTD:=TOTALYTD(sum(Data[Values]), Dates[Dates Column], "31/12/2014") where the last value is your year-end date for any year (I've used UK format which may not be approriate for you).

Finally you need one new measure which utilises the SWITCH function:

Selected Measure:=SWITCH([Selected Timeframe],1,[Sum YTD],2,[Sum 30 Days],3,[Sum 90 Days]).

What this measure will do is change the measure depending on the selected chiclet. E.g. If you press 'Past 30 Days', it create the value 2 in the [Selected Timeframe] measure. That will result in selecting the [Sum 30 Days] part of the [Selected Measure].

Now create the chiclet slicer and put the column 'Date Option' as the values (remembering to Sort By [No] to get the values in the right order).

Hope this helps - but let us know if still confused.

Thanks,

M