Return the date which is to the left of the selected column in a chart

[benmohagan]

Hi,

Can anyone help with what seems like a simple issue.  I have a simple date slicer which just has a list of dates - for this example it has 23rd, 24th, 25th, 26th and 27th March - 5 days.  

What I need to be able to do is if 2 or more dates are selected in the slicer (like the chart below)

If I select the 25th in this chart,  I want to be able to get data from the previous selected day,  in this case it would be the 23rd.   I cant seem to get the measure/process I'm working on to ignore the 24th.  How can I focus only on the dates selected and not all of the dates in the slicer (date table)

[AnalyticsWizard]

@benmohagan 

To achieve the desired result, you need a measure that dynamically responds to your slicer selection and identifies the previous date based on the slicer’s selected dates (not just the previous day). Here’s a step-by-step guide on how to create such a measure:

1. Use the `VALUES` function to create a virtual table containing the dates currently selected in the slicer.

2. Find the maximum date in this virtual table, which represents the latest selected date.

3. Identify the date that is directly before the maximum selected date within the context of the slicer selection.

Here’s a DAX measure that accomplishes these steps:

Previous Selected Date =
VAR SelectedDates = VALUES(DateTable[Date]) // Replace with your date column
VAR MaxSelectedDate = MAXX(SelectedDates, [Date])
VAR PreviousDate =
CALCULATE(
MAX(DateTable[Date]),
SelectedDates,
DateTable[Date] < MaxSelectedDate
)
RETURN
IF(
ISBLANK(PreviousDate),
BLANK(),
PreviousDate
)

When you use this measure, it calculates the previous selected date based on your slicer's selection. If you then need to retrieve data associated with this date, you can use this measure within a `CALCULATE` statement to adjust the context of your data retrieval accordingly.

Remember to replace `DateTable[Date]` with the actual name of your date column.

Keep in mind that if you have a continuous range of dates selected and you want to get the immediately preceding date, this measure will return the date directly before the maximum selected date within that range.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[benmohagan]

Hi AnalyticsWizard,  Thank you for the response!

Unfortunately,  this doesnt work though.  When I select a column in the chart visual,  the card where I placed the measure shows Blank,  whereas it should be 24th March