Using a date slicer to select if the date range is within the slicer

[Yxalitis]

I have an outage that starts in May, goes all though June, and ends in July

I need that outage to show up if I select May, June, OR July.

I report on many aspects of operations, open tickets, changes, outages, availability etc.

These are monthly reports, but i use a data table that allows any viewer to click a month, and see data for that month.

In particular I calculate the availability of services via outage records.

Duration is just datediff start end date

The Availability is simply:

% Availability = CALCULATE(DIVIDE(([Total Hours]-SUM(Outages[Duration (hrs)])),[Total Hours],0),USERELATIONSHIP('Date Table'[Date],Outages[End Date]))

Great, except I was using Outage[End] as my slicer association (Can't count an outage until it's over, I thought).

The problem is as in my example, what about an outage that crosses month boundaries.

When I select July, i get the full outage time of 1660 hours, which results in negative availability!

When I select May or June, I get 100% availability, when I should get 0% fir June, and nerly 0% for May, (i.e. the service was unavailable for the whole month)

When I want to see is:

If I select May, I calculate the duration as:
DATEDIFF( Outage[Begin],(end of month of selected Date in slicer),HOURS)

If i select June the duration is:

DATEDIFF( Beginning of selected date in slicer ,(end of month of selected Date in slicer),HOURS)

And if i select July, duration is:

DATEDIFF(Beginning of selected date in slicer,Outage[End},HOURS)

 The 3rd option (selecting the month that correlates to End date) works using this logic:

Begin Month Measure = MIN(Outages[Begin])

Availability Start Date = IF(

    [Begin Month Measure]<FIRSTDATE('Date Table'[Date]),

    FIRSTDATE('Date Table'[Date]),

    [Begin Month Measure])

Availability Duration = DATEDIFF([Availability Start Date],[Availability End Date],HOUR)

That works, but I can't use the same logic for moths that the End date does not appear in the slicer month, hence this question.

[hnguy71]

Hi @Yxalitis 

Assuming you have a disconnected slicer table, that's possible.

You would retrieve your selected month from this disconnected table and then build your evaluation on top of it. Do you already have one? If so, your measure can be similar to this:

VAR _SelectMonth = SELECTEDVALUE(DISCONNECTED_SLICER[YearMonth])
VAR _Dates = CALCULATETABLE(YOUR_DATE_TABLE, EOMONTH(_SelectMonth, -1) + 1, MAX(YOUR_DATE_TABLE[Date])) 

RETURN

CALCULATE( SUM(YOUR_VALUES_COLUMN[VALUE]), DATESBETWEEN(_Dates, MIN(YOUR_DATE_TABLE[Date]), MAX(YOUR_DATE_TABLE[Date])))

Of course, to better assist you, perhaps you may want to share some sample data, how your model looks, and expected results.

[Yxalitis]

Thanks for your reply.

I am not sure what you mean by 'disconnected'

I have a date table that has relationships to multiple date files in multiple tables, but mostly these are inactive until I trigger with USERELATIONSHIP measures.

Here's a one-liner example:

Number	Start	End
OUT0001234	1/05/2024 8:56	9/07/2024

I need that outage example above to show up if I select May or June or July in my global date slicer.

Every table and chart on the report uses this one date table, so I can’t simply make a new date table for this one measure, (or else I’d just build a separate measure that doesn't use my date table at all., the point is automation for end users to be able to select the date and see relevant data for that date, using one slicer, not one slicer for everything, and then another just for this table)

The goal is to select June in my one unified date table (that already filters dozens of graphs and tables simultaneously), and have that show me this above outage, where neither the start date nor end date are in June.

[hnguy71]

Hi @Yxalitis 

I totally understand. What you have so far is definitely a best practice to use time intelligence based off your date table. The problem is that since you're selecting the dates in range, you're subject to the filter limitations that's being applied and as much as you would like to remove, they are considered persistent.

This is where a disconnected table comes into play. When referring to disconnected, it means there are no relationships to the table itself, this being "disconnected" from your model. These types of tables are used to display/retrieve user parameters. You'll understand this a bit more down the line in your BI journey.

If this is your first time seeing the terminology "disconnected", good news is that you'll be more familiar with it very very soon. Here's a sample of what a disconnected table looks like:

Then, most would retrieve the user's selection from the disconnected slicer table:

A sample result, selected Feb 2024 would return me all of February until end of my date range:

And all that's left for you to do is build your time intelligence against your date table:

Understanding and using disconnected tables will be an essential to building more robust, dynamic, and highly interactive reports.

[Yxalitis]

Thanks again for you reply.

I can see what you are aiming at, but I don't think I've explained what i need clearly, my apologies.

I'll give you my full requirements.

I report on many aspects of operations, open tickets, changes, outages, availability etc.

These are monthly reports, but i use a data table that allows any viewer to click a month, and see data for that month.

In particular I calculate the availability of services via outage records.

Duration is just datediff start end date

I have this for B/H duration:

Business Hours Outage Duration =

VAR _Start = Outages[Begin]

VAR _End = Outages[End]

VAR _Workhours =

    SUMX (

        CALCULATETABLE (

            'Date Table',

            DATESBETWEEN ( 'Date Table'[Date], _Start, _End ),

            'Date Table'[Working Day] = "Yes"

            ),

        MAX ( MIN ('Date Table'[End],  _End ) - MAX ('Date Table'[Start],  _Start ), 0 ) * 24

    )

RETURN

    IF ( _Workhours, _Workhours, 0 )

(The date End date/time and Start date/time  are for setting business hours in the date table, don't get confused with the Outage Begin and End date/times)

The Availability is simply:

% Availability = CALCULATE(DIVIDE(([Total Hours]-SUM(Outages[Duration (hrs)])),[Total Hours],0),USERELATIONSHIP('Date Table'[Date],Outages[End Date]))

Great, except I was using Outage[End] as my slicer association (Can't count an outage until it's over, I thought).

The problem is as in my example, what about an outage that crosses month boundaries.

When I select July, i get the full outage time of 1660 hours, which results in negative availability!

When I select May or June, I get 100% availability, when I should get 0%, (i.e. the service was unavailable for the whole month)

When I want to see is:

If I select May, I calculate the duration as:
DATEDIFF( Outage[Begin],(end of month of selected Date in slicer),HOURS)

If i select June the duration is:

DATEDIFF( Beginning of selected date in slicer ,(end of month of selected Date in slicer),HOURS)

And if i select July, duration is:

DATEDIFF(Beginning of selected date in slicer,Outage[End},HOURS)

 The 3rd option (selecting the month that correlates to End date) works using this logic:

Begin Month Measure = MIN(Outages[Begin])

Availability Start Date = IF(

    [Begin Month Measure]<FIRSTDATE('Date Table'[Date]),

    FIRSTDATE('Date Table'[Date]),

    [Begin Month Measure])

Availability Duration = DATEDIFF([Availability Start Date],[Availability End Date],HOUR)

That works, but I can't use the same logic for moths that the End date does not appear in the slicer month, hence this question.

[hnguy71]

Hi @Yxalitis 

Thank you for your explanation.

 From what I can understand:

1. Business Hours is a calculated column seemingly with the right results you already have
2. You want to display the Availability % based on the selected month which is driven from two value columns (Total Hours and Business Hours)
3. Since you're using USERELATIONSHIP that means there's a primary relationship currently active that you need to consider / bypass.
4. You're stating May, June, July therefore you're likely most interested in outage availability extending outside of the current selected month or date
5. All your current measures already uses a date field from your actual date table

This isn't the end of the world and it can work granted that your date slicer is on the page itself and not on the filter pane. If that's the case, then all you would need to do add an ALL function to your calculation and it should work:

What this would do is to allow you to remove filter context applied at visual level and allow you to return values with a new date range. 

[Yxalitis]

Thanks very much for your ongoing attemtps to help me

This doesn't work at all

I have ONE ro for an outage, with Begin date in May, End date in July.

VAR _SelctedDate = SELECTEDVALUE('Date Table'[Date], MAX('Date Table'[Date]))
This just gives me start and end of the month selected, where do I enter the start or end of the outage itself?

There needs to be a seterministic IF statement in there somewhere, to work out that both the start date is in or prior to the selcted date, and the end date is in or after the selcted date. 
For May, the _start date should be the the start date of the outage, and the _end date should be the max of the selected value
Also, there is NOTHING to SUM, I do not have a list of hours to add up, I ONLY have the start end end date/times stamps to determine duration. What am I summing? there is no FACT(Value) to add! 

I need to have the measure determine if the OUTAGE was active (i.e. the selcted date on the slicer falls within the date range of the outage) ther rest I can work out.

[hnguy71]

hi @Yxalitis 

Sounds like you want to specifically set a start and end slicer. You can adjust your slicer to pick up both start and end dates like the image below:

And then you can find the min value, or in this case, your start date and the max value, or the maximum date.

Honestly, I'm getting more and more confused about the request. As you can tell, all my posts include images and relevant information that I feel would benefit you to understand my answers. It would be very helpful for myself and the community if you could upload a non-confidential version of your model, explain in greater detail your inputs and expected outputs in a table-like structure.

If you cannot upload a model, please explain in a manner (with supporting images) that is easy to digest for us to be better equipped in assisting you.

[Yxalitis]

Apologies, I'll try again.

I have one date table It has a slicer on the page I use to select the month of interest.

I have a measure to determine total number of hours in the month [TOTAL HOURS]

I have one Outage table.

Each outage is one row, that has Begin and End date

Duration is DATEDIFF(Begin,End,HOUR)

I associate End date to the date table

I use this measure to calculate availability:

% Availability = CALCULATE(DIVIDE(([Total Hours]-SUM(Outages[Duration (hrs)])),[Total Hours],0),USERELATIONSHIP('Date Table'[Date],Outages[End Date]))

However, sometimes an outage starts in one month, and ends in another.

e.g.

Number	Begin	End	Duration
OUT0003016	1/05/2024 8:56:00 AM	9/07/2024 1:00:00 PM	1660.1

CURRENT SITUATION

If I select May or June in my date slicer, this Outage will not show up

If I select July I get the entire duration of 1660.

DESIRED OUTCOME

If i select May, I get the duration based on Begin => 31/05/2024 23:59:59 (i.e. MAX or FINALDATE(Date)
If I select June I get the duration based on 01/06/2024 0:00 => 30/06/2024 23:59:59

If I select July I get the duration based on 01/06/2024 0:00 => End

That means I need intelligence to determine that:

The outage is active within the selected month., whole or partially, so it is selected with the Date Table slicer

If the Begin date is earlier than the current month, use the beginning of the selected month as start date

If the End date is later than the current month, use the end of the selected month as end date

To then calculate duration based on the above.

There is no value to sum, I need to CALCUALTE the duratin based on DATEDIFF, determined form the above requirements.

[hnguy71]

Hi @Yxalitis ,

Please take a look at this and see if this meets your criteria. I have built out a sample to help both myself and you understand.

I start off with a Date table that I'm using to slice my data connected to a table caled OUTAGE:

A slicer visual is added to the page returning the values of the month of a calendar. The sample data with result:

My measure used to achieve your desired result:

so... based on your expected output using my sample data, if I select May I should return both outages that has the month of May. Additionally, if the end dates is greater than the current month, then use the max date of the current selected month.

If I select June, I should also get all June records. Additionally, if the begin dates is less than the current month, then use the min date of the current selected month.

Finally, the duration is the difference between the two dates in evaluation via DATEDIFF function:

[Yxalitis · Accepted solution]

Thank you!
Using this as a guide, I created:

Outages in Period = SWITCH(

    TRUE(),

    Outages[Begin Month Measure] <= MIN( 'Date Table'[Date] ) &&  Outages[End Month Measure] >= MAX ('Date Table'[Date] ),1,

    Outages[Begin Month Measure] <= MIN( 'Date Table'[Date] ) &&  FORMAT(Outages[End Month Measure],"mmm yyyy") = FORMAT('Date Table'[Last Date],"mmm yyyy"),1,

    FORMAT(Outages[Begin Month Measure],"mmm yyyy") = FORMAT('Date Table'[Last Date],"mmm yyyy") && Outages[End Month Measure] >= MAX ('Date Table'[Date] ),1,

    0)

As a flag to include in table.

Turning columns into measures:

Begin Month Measure =MIN(Outages[Begin])

End Month Measure = MAX(Outages[End])

Availability Start Date = IF(

    Outages[Begin Month Measure] < STARTOFMONTH('Date Table'[Date]), STARTOFMONTH('Date Table'[Date]), Outages[Begin Month Measure])

Availability End Date = IF(Outages[End Month Measure] > MAX('Date Table'[Date]), MAX('Date

Duration in Month = DATEDIFF([Availability Start Date],[Availability End Date],HOUR)

That's (mostly) works!

I get the outages that were open in the month, and calcaulate the duration within that month!

However, occasionally it glitches:

Availability Start Date = 31/05/2024 8:56:00 PM (Correct)

Availability End Date  = 31/05/2024 12:00:00 AM (Correct)

Duration in Month = -20

Huh?

It does this whenever the start date is the last day of the month. and the end data is midnight of that day

Also 

Availability Start Date = 01/04/2024 12:00:00 AM (Correct) (i.e. beginning of month)

Availability End Date  = 30/04/2024 12:00:00 AM (Correct) (i.e. end of month)

Duration in Month = 696

Should be 720.

In fact even jsut working out Total Hours in a calendar month had to be tweaked:

Total Hours = DATEDIFF(FIRSTDATE('Date Table'[Date]), MAX('Date Table'[Date])+1,HOUR)

But I'll work that last bit out (unless you know the best practice for DATEDIFF!)

Thanks!