Dynamic Slicers with 2 dates

[johnbasha33]

Hi there esteemed community members.

i have a scenario as below
below is a sample data, i have share prices for Apple for different dates.

i duplicated date column as start date and end date.

i want to use these 2 dates as 2 slicers.

if user select start date and end date.

bottom table should display what is the share price of start date and end date in 2 columns.

basically i need 2 measures. 

i have been trying with calender table and different methods. it only works for start date, as soon as i select start date end date slicer unable to select.  any thoughts on this how to achieve ?

@amitchandak @lbendlin @mwegener @Jihwan_Kim @Arul 

Company	Share Price	Start Date	End Date	Date
Apple	$10.	1/5/2024	1/5/2024	1/5/2024
Apple	$15.	5/5/2024	5/5/2024	5/5/2024
Apple	$23.	12/5/2024	12/5/2024	12/5/2024
Apple	$25.	5/15/2024	5/15/2024	5/15/2024
Apple	$32.	5/18/2024	5/18/2024	5/18/2024

[hackcrr · Accepted solution]

Hi, @johnbasha33 

You need to drop one of the columns of end date or start date and store it in a table of calculations and put it into the slicer as shown in the following figure:

End day table = SUMMARIZE('Table','Table'[End Date])

I created the following two metrics:

share price of end date = 
VAR _end_day = SELECTEDVALUE('End day table'[End Date])
RETURN CALCULATE(SUM('Table'[Share Price]),FILTER(ALL('Table'),'Table'[End Date]=_end_day))

share price of start date = 
VAR _start_day = SELECTEDVALUE('Table'[Start Date])
RETURN CALCULATE(SUM('Table'[Share Price]),FILTER(
    ALL('Table'),
    'Table'[Start Date]=_start_day
)
)

I use both metrics in the table visual:

The results are as follows:

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GauravAher73 · Accepted solution]

Hi,

You can unpivot Start date and end date columns, so you will get values column in which all dates will be stored ,
now you can use values column in between slicer and get share price from date range

[johnbasha33]

Hi @GauravAher73  this seems to be a working solution, i am surprised it is that simple. would you please share me the pbix for the same? i will review it and will mark it as solution. 

[Arul]

@johnbasha33 ,

Could give us an example of what do you need in your visual and in which visual?

[johnbasha33]

Hi @Arul 

i am trying below, i want to see the start date price and end date price in a table. 

[Arul]

@johnbasha33 ,

Do you want them in two visuals?

[johnbasha33]

@Arul no, in a single visual with 2 columns, share price of start date and share price of end date

[hackcrr · Accepted solution]

Hi, @johnbasha33 

You need to drop one of the columns of end date or start date and store it in a table of calculations and put it into the slicer as shown in the following figure:

End day table = SUMMARIZE('Table','Table'[End Date])

I created the following two metrics:

share price of end date = 
VAR _end_day = SELECTEDVALUE('End day table'[End Date])
RETURN CALCULATE(SUM('Table'[Share Price]),FILTER(ALL('Table'),'Table'[End Date]=_end_day))

share price of start date = 
VAR _start_day = SELECTEDVALUE('Table'[Start Date])
RETURN CALCULATE(SUM('Table'[Share Price]),FILTER(
    ALL('Table'),
    'Table'[Start Date]=_start_day
)
)

I use both metrics in the table visual:

The results are as follows:

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

Hi @hackcrr  thanks for your time in looking into this, this seems to be a workable solution, let me review it a bit and will confirm.