topic Re: Dynamic Slicers with 2 dates in DAX Commands and Tips

Dynamic Slicers with 2 dates

johnbasha33 — Wed, 08 May 2024 06:20:54 GMT

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

Re: Dynamic Slicers with 2 dates

Arul — Wed, 08 May 2024 06:24:57 GMT

johnbasha33 ,

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

Re: Dynamic Slicers with 2 dates

johnbasha33 — Wed, 08 May 2024 07:01:54 GMT

Hi Arul

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

Re: Dynamic Slicers with 2 dates

Arul — Wed, 08 May 2024 07:12:49 GMT

johnbasha33 ,

Do you want them in two visuals?

Re: Dynamic Slicers with 2 dates

johnbasha33 — Wed, 08 May 2024 10:10:48 GMT

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

Re: Dynamic Slicers with 2 dates

hackcrr — Sat, 11 May 2024 11:02:44 GMT

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.

Re: Dynamic Slicers with 2 dates

GauravAher73 — Sun, 12 May 2024 19:05:58 GMT

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

Re: Dynamic Slicers with 2 dates

johnbasha33 — Mon, 13 May 2024 04:11:24 GMT

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.

Re: Dynamic Slicers with 2 dates

johnbasha33 — Mon, 13 May 2024 04:13:40 GMT

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.