Switching Values on a report with a splicer

Hi jcox,

I'm not sure what is the type of data you are analysing but I had the same issue regarding MTD, QTD, YTD values while on my search I have found a DAX webpage that allow me to do this. (sorry for not remebering the website address to be recorded that this is not my solution but I have learned it from others) below is what i do to do a dinamic change of the values based on a slicer. I have used this tecnhic with other type of parameters and worked fine with the proper adjustments.

Lets suposse you have the monthy sales and want to do a YTD, MTD, QTD calculation, and that we have a Sales table and a date table linked to sales.

First create a table with the parameters you want to use for your slicer that as the following structure:

Table Name = Timeframe

ID    Timeframe

1     MTD

2     YTD

3     QTD

IMPORTANT: dont' create any relationship with other tables must be a standalone table.

Within the table create the two following measures:

Selected Timeframe= MIN(Timeframe[ID])

Selection = IF (HASONEVALUE ( Timeframe[Timeframe]), VALUES ( Timeframe[Timeframe]))

The first measure gives you the minimum ID selected in the slicer.

The second one gives tells you what is the interval to consider when you select the values in the slicer if none are selected the full table is considered and then the minimum value is used based on the first measure.

On the table of the sales create the following measures:

SALESMTD = TOTALMTD(SUM(Sales[Sales_Value]),Dates[Date])

SALESQTD = TOTALQTD(SUM(Sales[Sales_Value]),Dates[Date])

SALESYTD = TOTALYTD(SUM(Sales[Sales_Value]),Dates[Date])

This makes the calculation for each parameter you have in the Timeframe table. Add also the following measure:

TotalSales= SWITCH([Selected Timeframe],1,[SALESMTD],2,[SALESQTD],3,[SALESYTD],BLANK(),[SALESMTD])

You evaluate the SelectedTimeframe and based on its number you return the corresponding formula, the last part is for you to have a value if no value in the slicer is chosend. This formula (SWITCH) allows to do the same as the IF formula but as more "power" in it (check: https://msdn.microsoft.com/en-us/library/gg492166.aspx and google it and you will find the use of this formula). 

Now you can use the Timeframe for your slicer and the TotalSales for you graphs, tables whatever when you change the slicer the calculations change accordingly.

This also works if instead of the MTD, QTD, YTD you replace the measures by Pieces and Value you have to do some measures like:

Table = SalesParameters

ID    Parameter

1     Pieces

2     Value

Selected SalesParameter= MIN(SalesParameter[ID])

Selection = IF (HASONEVALUE ( SalesParameter[Parameter]), VALUES ( SalesParameter[Parameter]))

SALESVALUES = SUM(Sales[Sales_Value])

PIECESVALUES = SUM(Sales[Sales_Pieces])

SalesTotal = SWITCH([Selected SalesParameter],1,Sales[PIECESVALUES],2,Sales[SALESVALUES],BLANK(),[PIECESVALUES])

Now if you do the SalesTotal in a graph when you SWITCH the slicer it gives you values or pieces.

Just one important thing is that the switch formula does not allow to have different types of data example numbers and percentages they all have to be in the same format.

Hope this helps and sorry for the long post. Any question please tell me.

MFelix