Forum Discussion

Yxalitis's avatar
Yxalitis
Icon for Helper I rankHelper I
1 year ago
Solved

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

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, ch...
Need Help
  • Yxalitis's avatar
    Yxalitis
    1 year ago

    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!
     
hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
1 year ago

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's avatar
Yxalitis
Icon for Helper I rankHelper I
1 year ago

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!