Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mooseman
New Member

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
Seward12533
Solution Sage
Solution Sage

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. 

View solution in original post

1 REPLY 1
Seward12533
Solution Sage
Solution Sage

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.