Minimum Date when non existing date is selected from date range.

[Jessica_17]

Hello All,

I require urgent help!!.
I have a table with this format,

Caategory	Date	Values
A	01-06-2024	0.6
A	01-07-2024	0.2
A	01-08-2024	0.5
A	01-09-2024	0.4
A	01-10-2024	0.6
A	01-11-2024	0.4
B	01-06-2024	0.8
B	01-07-2024	0.9
B	01-08-2024	0.7
B	01-09-2024	0.8
B	01-10-2024	0.8
B	01-11-2024	0.9

where date contains only first date of each month, when I add it into slicer, It shows range as this

When I select 14 July 2024, then the minimum date it gives 1 August 2024, where as I require 1 July 2024.

I have tried using separate calendar table as well, but it is not able to filter the data for other visuals accurately, even though the relationship is single - one to many.
And I want output in table visual like this:-

Caategory	Date	Values	Desired Date	Desired Value
A	01-06-2024	0.6	01-06-2024	0.6
A	01-07-2024	0.2	01-06-2024	0.6
A	01-08-2024	0.5	01-06-2024	0.6
A	01-09-2024	0.4	01-06-2024	0.6
A	01-10-2024	0.6	01-06-2024	0.6
A	01-11-2024	0.4	01-06-2024	0.6
B	01-06-2024	0.8	01-06-2024	0.8
B	01-07-2024	0.9	01-06-2024	0.8
B	01-08-2024	0.7	01-06-2024	0.8
B	01-09-2024	0.8	01-06-2024	0.8
B	01-10-2024	0.8	01-06-2024	0.8
B	01-11-2024	0.9	01-06-2024	0.8

Thanks

[burakkaragoz]

Hi @Jessica_17 ,

I see exactly what you mean with the date selection issue in your slicer. When you pick a date like 14 July 2024 (which doesn’t exist in your CategoryDate column), Power BI defaults to the next available date, which is 1 August 2024. But you want it to return 1 July 2024 instead—the closest previous available date.

To get this behavior, you’ll need to use a measure that dynamically finds the max available date that’s less than or equal to the slicer selection, for each category. Here’s how you can do it:

1. Create a Date Slicer Table
   Make sure your slicer uses a table with all possible dates in the range, not just the dates from your data table. This makes it easier to handle non-matching selections.

2. Build a Measure for the Desired Date
   You can use a DAX measure like this:

   DAX

   Desired Date = 
   VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
   RETURN
      CALCULATE(
         MAX('Table'[CategoryDate]),
         FILTER(
            'Table',
            'Table'[Category] = EARLIER('Table'[Category])
            && 'Table'[CategoryDate] <= SelectedDate
         )
      )

   • This measure finds the maximum date in your data that is less than or equal to the selected date, for each category.
   • Make sure to replace 'Calendar' and 'Table' with your actual table names.

3. Link the Calendar Table
   Connect your calendar table (used in the slicer) to your data table via the date column. The relationship should be single direction, from calendar to data.

4. Display the Desired Output
   Add your Category, Value, and the new Desired Date measure to your visual. Now, when you select any date in the slicer, your table should show the minimum available date less than or equal to the selection.

Note:
If you use a separate calendar table for the slicer, always use that table’s date in your measure logic. Also, if you want to show the “Desired Value” for that date, you can use another measure with LOOKUPVALUE or CALCULATE to fetch it.

If you need a more specific example with your column/table names, let me know! This approach should get you the output you want, even if the selected date isn’t in your main table.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Jessica_17]

HI @burakkaragoz 

Thanks for the reply, but we cannot use earlier function in calculated measure, is there any other way to do it?

Thanks