Create Period Slicer in PowerPivot

[slesko]

Hi,
I want to create a (WTD, MTD, YTD) slicer. My data structure is simple. I have a date column, Total time column, and Location column. The overall goal is to create a PowerView chart showing the total time by location but the kicker is I also want to be able to select on my three button slicer "WTD, MTD, YTD" to filter the chart. I also have already created a Date table in PowerPivot. I have been playing around with the SWITCH() function but failing miserably. Simply, right. Thanks for all your help.
Steve

 

[theBardd]

I think that you want a disconnected slicer here.

Setup a small table in Excel, say called ReportType, with Id 1-n, and Type for each of the slicer values. Import that to PowerPivot (don't try to connect to any other table).

Add a measure, like
SelectedOption:= MIN( ReportType[Id] )

Assuming you already have measures such as TotalSales, MTD, QTD, and YTD then your display measure would be something along the lines of

=IF( VALUES( ReportType[Type] )="MTD", [MTD],   IF( VALUES(  ReportType[Type] )="QTD", [QTD],
   IF( VALUES( ReportType[Type] ) ="YTD", [YTD],
   [TotalSales])))

 

[slesko]

Thank you for the help but I'm still having an issue. Below is my measure. Thanks

Periods:=IF(VALUES(Period[Period])="WTD",[WeekToDate],IF(VALUES(Period[Period])="MTD",[MonthToDate],IF(VALUES(Period[Period])="YTD",[YearToDate],ALL())))

Below is WeekToDate measure

WeekToDate:=CALCULATE(COUNT(ETR[Outage no]),FILTER(etr,WEEKNUM(ETR[Start Date])=WEEKNUM(TODAY())))

When I created the filter nothing happens to my charts when I select WTD. I did make sure that there is a "start Date" that is in this week.

Thanks,

 

[theBardd]

It's a little difficult to envisage the problem; would it be possible to upload the file somewhere?

 

[slesko]

Sorry theBardd. I've got basically a Name, StartDate, EndDate. I want to create a slicer in powerview that when I click on MTD button it just shows me the month to date items on my charts. I also have created a Period table which has PeriodId and PeriodName that I do not have linked. The data inside the Period table is PeriodId 1 - PeriodName WTD, and PeriodId 2 - PeriodName MTD, and PeriodId 3 - YTD. I'm hope this helps some. Again. Thank you.