Forum Discussion
Date Slicer Using Two Different Date Fields
- 8 years ago
The trick to this is to use disconnected slicers or paramers to define the Start and End dates and then use the results the user selects in measures to filter your results. Here is an example. In this case I have disconnected slicers for Half and Years and use SELECTEDVALUE to harvest the user results and set a default if nothing is selected. [Latest Half] is a measure that calcualtes the half from the date table of the last month that has Actuals in it. You could use half of current month or some other method.
Latest Month = CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast)) Latest Half = LOOKUPVALUE(FST_Date_Dim[Fiscal_Year_Half_Txt],FST_Date_Dim[Calendar_Date],[Latest Month])
Then in my measure I use the SELECTED values in my calculation in this case I then calcualate the [Last Month] in the data for the selected Half or Year to find the Last Month that has "Actuals" in it.
Last Month Actual = Var Selected_Half = [Selected Half] VAR Selected_Year = [Selected Year] RETURN IF(not(ISBLANK(Selected_Year)), CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),FST_Date_Dim,FST_Date_Dim[Fiscal_Year_Txt]=Selected_Year,vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast[Parent Customer Name]),all(Regions),all(Groups),all(SalesPersons)) , CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),FST_Date_Dim,FST_Date_Dim[Fiscal_Year_Half_Txt]=Selected_Half,vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast[Parent Customer Name]),all(Regions),all(Groups),all(SalesPersons)) )Then I calculate the measures I use in my visuals from the Last Month and Start Months I calcualted. I know it seems redundant to use variables to defined as single measure but it made it easier to reuse the formula in simlar calcucations where I can use different Start and End dates.
Actual = VAR Last_Month = [Last Month] VAR Start_Month = [Start Month] RETURN CALCULATE( SUM(vw_FST_Budget_vs_Actual_vs_Forecast[Amount]),vw_FST_Budget_vs_Actual_vs_Forecast[Account Type]="Revenue",vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]<=Last_Month&&vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]>=Start_Month)
I know this situation is a more complex but its what I had handy it should demonstrate the principle. In your case you will need to create two disconnected tables that contain the ranges of the data you want the user to select from. If you can have calendar table already you can use Create Table and then write a mesure to harvest the user input or set a default.
StartDates = VALUES(Date[Date]) //TABLE Query StartDate = SELECTEDVALUE(StartDates[Date],now()-30) // Measure to harvest date from input with a default of today - 30 days
Then use logic similar to what I used in [Actual] to calculate your values.
The trick to this is to use disconnected slicers or paramers to define the Start and End dates and then use the results the user selects in measures to filter your results. Here is an example. In this case I have disconnected slicers for Half and Years and use SELECTEDVALUE to harvest the user results and set a default if nothing is selected. [Latest Half] is a measure that calcualtes the half from the date table of the last month that has Actuals in it. You could use half of current month or some other method.
Latest Month = CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast)) Latest Half = LOOKUPVALUE(FST_Date_Dim[Fiscal_Year_Half_Txt],FST_Date_Dim[Calendar_Date],[Latest Month])
Then in my measure I use the SELECTED values in my calculation in this case I then calcualate the [Last Month] in the data for the selected Half or Year to find the Last Month that has "Actuals" in it.
Last Month Actual = Var Selected_Half = [Selected Half] VAR Selected_Year = [Selected Year] RETURN
IF(not(ISBLANK(Selected_Year)),
CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),FST_Date_Dim,FST_Date_Dim[Fiscal_Year_Txt]=Selected_Year,vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast[Parent Customer Name]),all(Regions),all(Groups),all(SalesPersons))
, CALCULATE(max(vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]),FST_Date_Dim,FST_Date_Dim[Fiscal_Year_Half_Txt]=Selected_Half,vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",all(vw_FST_Budget_vs_Actual_vs_Forecast[Parent Customer Name]),all(Regions),all(Groups),all(SalesPersons))
)Then I calculate the measures I use in my visuals from the Last Month and Start Months I calcualted. I know it seems redundant to use variables to defined as single measure but it made it easier to reuse the formula in simlar calcucations where I can use different Start and End dates.
Actual = VAR Last_Month = [Last Month] VAR Start_Month = [Start Month] RETURN CALCULATE( SUM(vw_FST_Budget_vs_Actual_vs_Forecast[Amount]),vw_FST_Budget_vs_Actual_vs_Forecast[Account Type]="Revenue",vw_FST_Budget_vs_Actual_vs_Forecast[Scenario]="Actual",vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]<=Last_Month&&vw_FST_Budget_vs_Actual_vs_Forecast[Report Month]>=Start_Month)
I know this situation is a more complex but its what I had handy it should demonstrate the principle. In your case you will need to create two disconnected tables that contain the ranges of the data you want the user to select from. If you can have calendar table already you can use Create Table and then write a mesure to harvest the user input or set a default.
StartDates = VALUES(Date[Date]) //TABLE Query StartDate = SELECTEDVALUE(StartDates[Date],now()-30) // Measure to harvest date from input with a default of today - 30 days
Then use logic similar to what I used in [Actual] to calculate your values.