Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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.
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
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
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.
Could give us an example of what do you need in your visual and in which visual?
Do you want them in two visuals?
@Arul no, in a single visual with 2 columns, share price of start date and share price of end date
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.
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.
User | Count |
---|---|
52 | |
46 | |
20 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
31 | |
19 | |
18 |