cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mooseman Occasional Visitor
Occasional Visitor

Date Slicer Using Two Different Date Fields

Hi everyone,

 

First time poster. I've read some solutions but nothing seems to be doing the trick. Here's my situation. I have a dashboard with several  matrices and other graphs. I have some high level filters for the dashboard such as "Building" and "Room" that allow me to filter all the data as I select them. I also want a date slicer where I can simply drag the slider or type the date and it'll filter for all the graphs and matrices.

 

The customers IDs I want to select each have a "BeginDate" and an "EndDate". I want to be able to create a slicer that incorporates all IDs that fall between both those date fields. So my question is is there a way to create a single date slicer (the one where you can drag the slider back and forth) which will pull back all records that fall within both the "BeginDate" and "EndDate" fields?  For example,

 

ID         BeginDate           EndDate

1           6/8/2018             6/9/2018

2           6/9/2018             6/9/2018

3           6/9/2018             6/11/2018

4           6/11/2018           6/12/2018

5           5/10/2018           6/20/2018

6           5/20/2018           6/21/2018

 

 

So if I selected the date on my slicer as 6/11/2018 then I should be able to see IDs 3, 4, 5, 6 in my visuals. because they all fall between the two date ranges (BeginDate and EndDate). Is there a way to do that?

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: Date Slicer Using Two Different Date Fields

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])

 

 

capture20180720220705523.png

 

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. 

1 REPLY 1
Seward12533 New Contributor
New Contributor

Re: Date Slicer Using Two Different Date Fields

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])

 

 

capture20180720220705523.png

 

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. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 402 members 3,667 guests
Please welcome our newest community members: