Create a table of dates based on slicer selection to give start and end dates

[timknox]

hi,

I am new to PowerBI so would welcome some help please!

nameTable:

Name	Start Date	End Date
John	01/05/2012	23/05/2023
Paul	25/09/2015	01/06/2024
Peter	31/03/2016	02/09/2021
Iain	01/08/2013	03/08/2026

I have a simple table (called nameTable), and a slicer based on the column:  Name

What i am trying to create is also a Date Slicer linked to a calendar table, where the start and end date comes from my nameTable.

So basically, if i select Paul in the slicer, a seperate Date Slicer will have the Start and End dates 25/8/2015 and 01/06/2024 respectively.

Welcome any help....

Many thanks

Tim

[dujos]

Hello team, i don't know if this will works as you want but try to do this:

I have used 3 Slices - First for Name Column, second for Start Date and Third for End Date Column. After this i have put a Square form without fill so the result is showed in image below:

After creating this elements you have to Edit interaction of Stard Date Slicer and End Date Slicer to Name Slicer as showed as follow:

After this, when you select a name on Name Slicer for example John, the result is that:

I have selected John and the Slicer Start Date and End Date are showing only the dates of John.

I hope i've helped.

[timknox]

Thanks for that.......

I really did not want to have another slicer......

I was trying to do it with this:

Table =

VAR StartDate= Selector[Start Date]
VAR EndDate= Selector[End Date]

RETURN

CALENDAR(StartDate,EndDate)

It works with the Start Date (if i replace the above with VAR EndDate="31/3/2022") but not as detailed above!

I am getting so frustrated!

Any help please.......

Thanks

[Arentir]

Hi @timknox

Unfortunately that won't work using a table, it is loaded once and slicers won't affect it.

Also, Slicer only work with column value. However you could have a table chart with your date field and a measure as a visual filter, would that do?

[timknox]

is it possible to show me how to do that - sorry i am not good at this!

[Arentir]

Sure,

Have a look at the pbix in the link

https://1drv.ms/u/s!As5oZCclkGkWgSTanlDS9mHENg4u

I have created the following measure

Is In Date Range =
IF(
AND(SELECTEDVALUE('CALENDAR'[Date])>=MIN(Employee[Start Date]),
SELECTEDVALUE('CALENDAR'[Date])<=MAX(Employee[End Date])
),
"Y",
"N"
)

Beware that if you select two employees or more, your date range will be the earliest/latest of any of them combined. You could use SELECTEDVALUE to go for an alternative value.

I have added this measure to a table chart and have a filter on the visual.

[timknox]

Wow - i think you have nearly solved it!

Can i now turn that into a date slicer?

[Arentir · Accepted solution]

This is the closest I can get to dynamic date.

I see no way to have this in a slicer because it doesn't work with measures, sorry about that!

[LivioLanzo]

what is the end goal?