cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
timknox Regular Visitor
Regular Visitor

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

hi,

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

 

nameTable:

 

NameStart DateEnd Date
John01/05/201223/05/2023
Paul25/09/201501/06/2024
Peter31/03/201602/09/2021
Iain01/08/201303/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

1 ACCEPTED SOLUTION

Accepted Solutions
Arentir Regular Visitor
Regular Visitor

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

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!

 

8 REPLIES 8
LivioLanzo Super Contributor
Super Contributor

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

what is the end goal?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

dujos Frequent Visitor
Frequent Visitor

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

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:

img1.png

 

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

img2.png

 

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

 

 

img3.png

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 Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

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

Arentir Regular Visitor
Regular Visitor

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

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.

image.png

 

image.png

timknox Regular Visitor
Regular Visitor

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

Wow - i think you have nearly solved it!

 

Can i now turn that into a date slicer?

Arentir Regular Visitor
Regular Visitor

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

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!

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 57 members 1,122 guests
Please welcome our newest community members: