Get all Records between two dates based on a slicer selction

[Gresi]

Hello at all,

i'm looking for a possibility to get all records between two dates from a slicer.

That's my slicer for selecting min and max-date.

Her two measures for getting min and max from slicer:

minDate_Selected = CALCULATE(MIN(CalendarDays[Day]);ALLSELECTED(CalendarDays))

maxDate_Selected = CALCULATE(MAX(CalendarDays[Day]);ALLSELECTED(CalendarDays))

After that i want to filter the table for getting all records between.

But it looks like the values minDate_Selected and maxDate_Selected will not be taken over for calculation.

Now i want to show all records in a timeline like this.

It would be great if anyone can help me please.

Thank you in advance, Gresi

[amaleranda]

@Gresi 

also I noticed in your min/max calculation

you might need to clear all the visual level filters coming from the related visuals. Removing visual level filters really based on the visuls that you are using on the report. 

minDate_Selected = CALCULATE(MIN(CalendarDays[Day]);ALLSELECTED(CalendarDays), all(mergedTable))

maxDate_Selected = CALCULATE(MAX(CalendarDays[Day]);ALLSELECTED(CalendarDays), all(mergedTable))

[amaleranda]

@Gresi 

If you are using direct query then there is an issue with milliseconds in the time stamps. I had this issue when I try to calculate min and max time in a DQ dataset. Its a analysis services issue @marcorusso has a white paper about the same issue. 

If that is the case you may want to truncate the ms part of the timestamp and use the tuncated timestamp in the min and max calculation and comparison. 

[AlB]

Hi @Gresi 

It loos like you are creating a calculated table. These are static and will not be affected by slicers. You could use the start and end date in MergedTasks directly on the slicers

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Gresi]

Hi AIB,

thanks for the answer.

Should i use two slicers ? One for start and one for end ?

And how should i substitute start an end when they are overlapping like this ?

I can not do like:

substitutedStart = IF(MergedTasks[start] < [minDateTime_Selected]; [minDateTime_Selected]; MergedTasks[start])

substitutedEnd = IF(MergedTasks[end] > [maxDateTime_Selected]; [maxDateTime_Selected]; MergedTasks[end])

because i don't have minDateTime_Selected and maxDateTime_Selected, both are measures.

I do need this for every record in my table because the timeline shows substitutedStart  to substitutedEnd for each record.

If any start or end overlapping minDateTime_Selected or maxDateTime_Selected it should get substituted with it.

I hope you now what i mean.

Thanks, Gresi

[AlB]

@Gresi 

Have a look aat what's done here with the filter for the visual and see if it helps:

https://community.powerbi.com/t5/Desktop/How-to-check-if-table-of-date-ranges-overlap-with-dates-sel...

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Gresi]

@AIB

Hi, thanks for your answer.

But i can't do it.

Would you download the example to show me how i can i solve the issue ?

https://lmy.de/oK3No

I only want to see all records of the 06.08.2019.

There should be two replaced values.

The start 05.08.2019 23:21:25 - end 06.08.2019 01:45:15 should change to start 06.08.2019 00:00:00- end 06.08.2019 01:45:15 and

start 06.08.2019 19:17:36- end 07.08.2019 05:43:59 should change to start 06.08.2019 19:17:36- end 06.08.2019 23:59:59.

It would be nice if you could solve that.

Thanks, Gresi

[v-juanli-msft]

Hi @Gresi 

When clicking on the link, it blocks so i can't find any sample data or screenshots.

Is your sample data like

start date	end	index
8/5/2019 23:21	8/6/2019 1:45	1
8/6/2019 19:17	8/7/2019 5:43	2

What is your final expected result?

calcualte days for each index which is in the period of selected min and max date?

Best Regards
Maggie

[Gresi]

Hi Maggie,

sorry, my data looks like this.

I want to have all records from 06.08.2019 00:00:00 to 06.08.2019 23:59:59.

The start value from the first row and the endvalue from the last row should be replaced because they are overlapping.

The yellow rows should be displayed in a diagram like asTimeline.

The timerange should be adjusted with a slicer.

I hope you know what i mean.

Thanks, Gresi

[Gresi]

Hi @Maggie,

does this link work ?

https://drive.google.com/file/d/1F7mPUSSWg4hMklvgrl2UeZYAa6UIg_44/view?usp=sharing

Thanks, Gresi

[venal]

@Gresi 

Thanks for sharing the PBIX file.

I see that there is no relationship in between the tables, can you please create a relationships in the data model and try.

[Gresi]

Hi @venal,

thanks for your reply !

Now i have two slicers, one for startdate and one for enddate.

Startdate has a relationship to MergedTasks[startDate] and enddate has a reationship to MergedTasks[endDate].

The slicer for the hours can be neglected.

https://drive.google.com/file/d/11aRKfPvHYPRkDl0Rp7ZxU7nkP8cdeLFH/view?usp=sharing

I only what to show the tasks between these dates including the trimmed tasks.

For example:

startdate -> 06.08.2019

enddate -> 07.08.2019

Now all tasks from 06.08.2019 are be displayed.

The first and the last task should be cut off because the first start on the 05.08.2019 and the last ends on the 07.08.2019.

I only wanna see all tasks from the 06.08.2019.

After cut off the first task should start on the 06.08.2019 00:00:00 and the last task should end on the 06.08.2019 23:59:59.

I hope you understand.

Thanks, Gresi

[v-juanli-msft]

Hi @Gresi 

First delete the relationship so to have model below

Create a caluclated column in "merged task" table

date-time = [Date]&" "&[Time]

Change it to date time format

Then Create measures

Measure =
VAR start1 =
    MIN ( CalendarStartDays[startdate] )
VAR end1 =
    MAX ( CalendarEndDays[enddate] )
RETURN
    IF (
        MAX ( MergedTasks[Date] ) > start1
            && MAX ( MergedTasks[Date] ) < end1,
        1,
        0
    )

In this way, it will filter only 2019/8/6.

What do you like for "hour" slicer?

Use the slicer to limit the end hour of the that date(2019/8/6)?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gresi · Accepted solution]

Hi at all,

thanks for the answers.

An addendum again.

In the timeline below, the values of both substitutedStart and substitutedEnd are shown.

minDateTime_Selected and maxDateTime_Selected are determined as follows.

In future, however, minDate_Selected and minTime_Selected as well as maxDateSelected and maxTimeSelected should no longer be hard coded but be selectable via a slicer.

My main problem is that I get the values that come from a slicer not in the editor.

So I can not expect it.

Maybe anyone knows.

Thanks, Gresi

[Gresi]

Hello everybody,

can someone help me please ?

That would be wonderful, I can not get any further

Thank you in advance,

Gresi

[v-juanli-msft]

Hi @Gresi 

First please note:

calculated columns can't change with slicer.

We can't add date and time by "+", it doesn't produce a correct date time format value.

I am working to find a solution for you, sorry for late.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.