Select a Date period with time factored in

haroonali1000 · March 19, 2021, 4:24pm

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
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:

eg

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

BrianJ · March 19, 2021, 6:20pm

@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

jbressan · March 19, 2021, 6:51pm

Hi @haroonali1000

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

[Forum Time -

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

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)

EnterpriseDNA · March 23, 2021, 1:09am

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!

EnterpriseDNA · March 24, 2021, 1:30am

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.

EnterpriseDNA · March 25, 2021, 4:14am

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!

haroonali1000 · March 26, 2021, 8:14am

Thanks All.

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

tej · October 29, 2021, 7:25am

Hi Haroonali1000,

I am facing the same issue. Can you please help me with the solution. Should be able to see the records from start date and end date with different time intervals. If the user selects the start time as 28/10/2021 11:00 AM and end time as 29/10/2021 2:00 AM of end date they should see the results including minutes as well. please suggest me

Keith · October 29, 2021, 9:43am

Please start a new topic as this posting is already solved. Your question may not get solved as we don’t go back to solved solutions.

Please also delete your posting from this solved posting

Main menu …hover over the blue circle bottom right corner which will change to new topic and click on it.

thanks
Keith