Latest Enterprise DNA Initiatives

Select a Date period with time factored in

Hi All,

I have been doing some research and have come a little stuck and needed some help/guidance.

I have a model where I am using both date and time dimensions.

what I am trying to do is select multiple dates so in this example PBIX I am selecting 17th 18th march.

What I would like to do is define when the start time is for the 17th and end time on the 18th.

Eg
image Period selected

I would then like to pick a start time for the 17th lets say 06:00 and an end time for the 18th lets say 17:00 and return all rows in my fact between this period.

At the moment when I filter on hour:

egimage

I would only see the records of 06:00 to 17:00 for each of the days selected.

Any ideas or suggested reading on achieving this in Power BI.

Test Pbix below;

Forum Time.pbix (1.0 MB)

Many thanks in advance.

Haroon

@haroonali1000,

I don’t think active slicers are going to work in this case. I think what you’ll need to do is create a disconnected timetable and then harvest start and end times from two list or drop down slicers connected to that disconnected timetable. Harvesting the values from the date slicer and the disconnected time slicers will allow you to build the filter conditions into your measures to select only the desired records.

I hope this is helpful.

– Brian

2 Likes

Hi @haroonali1000

This is what you looking for?, If the answer is yes, please review the file.

[Forum Time -

  1. Split EndDateTime: EndDateTime.1 is date and EndDateTime.2 is time
  2. Add Hour Slicer Hours = GENERATESERIES(1,24,1)
  3. Remove Times Slicer
  4. Create two measures Valid Period and Duration
  5. Set the Filter for Valid Period equal = 1

image

Valid Period =
VAR _Min =
    MIN ( Hours[Hours] )
VAR _Max =
    MAX ( Hours[Hours] )
VAR _ST =
    SELECTEDVALUE ( 'Fact'[Start Time] )
VAR _ET =
    SELECTEDVALUE ( 'Fact'[EndDateTime.2] )
RETURN
    IF ( AND ( HOUR ( _ST ) >= _Min, HOUR ( _ET ) <= _Max ), 1, 0 )

and

Duration = 
SUMX (
    FILTER (
        SUMMARIZE (
            'Fact',
            'Fact'[Start Date],
            'Fact'[Start Time],
            'Fact'[EndDateTime.2],
            "@Duration", SUM ( 'Fact'[Duration] ),
            "@Valid Period", [Valid Period]
        ),
        [@Valid Period] = 1
    ),
    [@Duration]
)

Forum Time - JoseBressan.pbix (1.0 MB)

2 Likes

Hi @haroonali1000, did the response provided by @jbressan and @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @haroonali1000, we’ve noticed that no response has been received from you since the 20th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @haroonali1000, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Thanks All.

Managed to get a solution working using a cross between what @jbressan and @BrianJ :slight_smile:.