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

[v-priyankata · Accepted solution]

Hi  @Jessica_17 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@burakkaragoz thanks for sharing the inputs to the community.
I really understand the issue you're facing it's a common functioning when your data only includes the first day of each month (such as 01-07-2024), while the date slicer allows users to select any date (like 14-07-2024). As a result, Power BI looks for the next available date after the one selected and moves directly to 01-08-2024. This causes your data not to align as expected.
By following the below steps you might resolve your issue:

1. Create and Build a calendar table that only includes the 1st day of every month not as daily calendar format. That way, the slicer stays in sync with your actual data.
2. Use that calendar in your slicer and replace your current slicer with this new calendar table. Then users can only pick from the same dates that actually exist in your data (like 01-07-2024, not 14-07-2024).
3. Link this calendar to your data and create a one-to-many relationship from the calendar’s Date to your table’s Date column. That’ll let Power BI know how to filter everything correctly.
4. Create a measure to show the value for the first date and you can write a simple DAX measure that always pulls the value from the earliest date visible in your filtered table as below:

Desired Value =

CALCULATE (

   FIRSTNONBLANK ( YourTable[Values], 1 ),

   FILTER (

       YourTable,

       YourTable[Date] = MIN ( YourTable[Date] )

   )

)

5. Use it in your matrix or table visual, show:
   • The original Category, Date, and Values
   • Add two extra columns:
     • Desired Date → just MIN(YourTable[Date])
     • Desired Value → the measure above
       
       

If this information is helpful, please “Accept as solution”  to assist other community members in resolving similar issues more efficiently.
Best Regards,
Priyanka
Community Support team