Slicer to include "open" cases based on start and end date

[sligo-ali]

Hello all,

I have a set of open "cases" for an office. Cases in my database will always have a start date but may or may not have an end date (in this case they are "Open")

I'm new to PowerBI and I see how to add a slicer for either start date or end date, but essentially, I am hoping I can create a slicer that will look at the case's Start and End date rather than one or the other to either include or exclude the case data from the dashboard.

For example: If Case A was open from January 1 to June 15, data for this case across the Dashboard would appear if I chose January, February, March, April, May, or June as a month in my slicer. As it stands with my Start Date slicer, it data with this case would only appear if I chose to select January in my slicer. 

Another example, I would want data related to Case B (started on August 20, still in progress now in November) to show if I were to select any combination of August, September, October, November in the slicer.

Thank you in advance for the help.

A

[gmsamborn · Accepted solution]

OK @sligo-ali 

Again, this SEEMS to work.

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open <= _Max
			&&  ( _Close = BLANK() || _Close >= _Min ),
        1,
        0
    )

Open cases - Date.pbix

Let me know if it works for you.

[gmsamborn]

Hi @sligo-ali 

I hope I'm understanding your requirements.  If you are using a Date table, the following could be used to filter your visuals.

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open >= _Min
            && COALESCE( _Close, _Max ) <= _Max,
        1,
        0
    )

You aren’t clear about what type of slicer you want. You mention “a slicer that will look at the case's Start and End date” and then later mention slicing by Month. With Date as your slicer field, the default is a “Between” slicer. When using Month, you can select and unselect any months (not necessarily contiguous), but not a start and end month if you want the interim months to be selected automatically.

Take a look at these and let me know if you have any questions.

Open cases - Date.pbix

Open cases - Month.pbix

[sligo-ali]

Hello @gmsamborn thank you for your response. I am going to open those files and see if they are the solution I am looking for. In the meantime, I've made this image to hopefully give further clarity to my question.

Thank you,

A

[sligo-ali]

Hi again @gmsamborn 

The files you sent are not quite the solution I was looking for -- 

for example, in either of the files, when I adjust the start date to Feb 1 (or exclude January but keep February in the Month picker), CaseID 1 should remain in the table because it was open after Feb 1 / during the month of February. In the files you sent, CaseID 1 is excluded when I move the date picker to Feb 1 or exclude January from my selection.

So to further clarify, I suppose I am asking for a slicer that will include cases that were open at any time during the selected timeframe.

I've included more examples in my previous reply, as well.

Thank you again for your input

A

[gmsamborn]

@sligo-ali 

My mistake.  Try this.  (I just had a sign reversed.)

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open >= _Min
            && COALESCE( _Close, _Max ) > _Max,
        1,
        0
    )

 Open cases - Date.pbix

Open cases - Month.pbix

I think it is right now.

[sligo-ali]

Hey again @gmsamborn 

I think there may have been another error, because now more cases are being excluded that should not be. I used the Open cases - Date file and when I moved the slider all the way to January 1 to December 31 (entire timeframe), no cases at all are included.

[gmsamborn]

OK.  Only the cases with NO close date should be showing when the end date is 12/31/2023.

Try this

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open >= _Min
            && _Open < _Max
            && COALESCE( _Close, _Max + 1 ) > _Max,
        1,
        0
    )

[sligo-ali]

Hi there @gmsamborn 

Cases with no end date should only be included if they were open at any point in the timeframe. I've created a graphic below to show cases included vs. excluded in a more visual manner. Green would show on dashboard and red would be excluded.

Below, there are two cases with no end dates (Case G and Case I) -- Case G is included in the dashboard because it was open during the timeframe, while Case I is excluded because it was not open during the timeframe.

Case F is also a special case because the Start and End date both fall outside of the time frame on the slicer, but the case should still be included because it was open during the time frame.

[gmsamborn]

Hi @sligo-ali 

Based on your data, this seems to work except for Case I.  I'll keep testing and also make any changes to 'Open Cases - Month.pbix .

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        ( ISBLANK( _Close ) && _Open <= _Max )
            || ( _Close > _Min && _Open <= _Max ),
        1,
        0
    )

Open cases - Date.pbix

[sligo-ali]

Hi @gmsamborn thank you so so much. This is almost exactly the behavior I'm desiring.

The only things I noticed were 1) that in the file you shared has an end date of 12/31/2023 for Case I (not a huge issue).

2) I'm hoping for a case to be included if the end date is within the timeframe of the filter. For example: when I use the timeframe 1/31 to 12/31, Case A should still be included because althought it closed on 1/31, it was still "open" for some portion of that day.

[gmsamborn]

Yes.  Currently all examples work except for case I.

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        ( ISBLANK( _Close ) && _Open <= _Max )
            || ( _Close > _Min && _Open <= _Max ),
        1,
        0
    )

Not sure why that last one is a problem but I'll keep at it.

[gmsamborn · Accepted solution]

OK @sligo-ali 

Again, this SEEMS to work.

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open <= _Max
			&&  ( _Close = BLANK() || _Close >= _Min ),
        1,
        0
    )

Open cases - Date.pbix

Let me know if it works for you.

[sligo-ali]

Okay, this is so close! @gmsamborn The last thing I see is that the Include column is functioning completely as desired but those with a 0 are not being removed altogether

[gmsamborn · Accepted solution]

Sorry.  That was for demonstration.  

You can use _Include in the Filters pane for a visual., matching 1.

(I've updated the link.)

[sligo-ali]

Thank you!

_Include = 
VAR _Min = MIN( 'Date'[Date] )
VAR _Max = MAX( 'Date'[Date] )
VAR _Open = SELECTEDVALUE( 'Cases'[OpenDt] )
VAR _Close = SELECTEDVALUE( 'Cases'[CloseDt] )
RETURN
    IF(
        _Open <= _Max
			&&  ( ISBLANK( _Close ) || _Close >= _Min ),
        1,
        0
    )

This is perfect! The link is fixed. Thank you!

[Ashish_Mathur]

Hi,

Share some data to work with and for that sample data, show the expected result.

[sligo-ali]

Hello,

Does this help?

Thank you,

A