The x axis should start by default at 8 am for the selected date and end at 7 am the next day

[Sri_Anusha]

I have a date column which I am using as a dropdown. I have many visualizations like stacked column chart , line and clustered column chart.
Firstly, whenever i select any date the values should include from 8 AM of the selected date to next day 7.59 AM .The charts should display x axis from 8 AM on selected date to next day 7 AM that is a 24 hour period by default for any selected date. 
Secondly, always the default selected date should be yesterday in the dropdown and the data in the charts also should be the same.

Date	Sum of R	Date time	Hours
09/30/2024	0.85	09/30/2024 8:21	8:00 AM
09/30/2024	1.85	09/30/2024 8:22	8:00 AM
09/30/2024	2.98	09/30/2024 8:23	8:00 AM
09/30/2024	4.05	09/30/2024 8:24	8:00 AM
09/30/2024	10.77	09/30/2024 18:44	6:00 PM
10/01/2024	4.55	10/01/2024 0:54	12:00 AM
10/01/2024	3.82	10/01/2024 7:27	7:00 AM
10/01/2024	3.03	10/01/2024 7:28	7:00 AM
10/01/2024	3.77	10/01/2024 7:58	7:00 AM
10/01/2024	2.8	10/01/2024 7:59	7:00 AM

[v-stephen-msft]

Hi @Sri_Anusha ,

I think lbendlin's insight is great.

I made an example for this.

1.Create a disconnected table with start datetime and end datetime.

Start = [Date]+1/3

End = [Date]+1+1/3

2.Now you can just create a measure as the visual-level filter.

Measure = var _start=MAX('Date'[Start])
var _end=MAX('Date'[End])
var _date=MAX('Table'[Date time])
RETURN IF(ISFILTERED('Date'[Date]),IF(_start<=_date&&_date<_end,1),1)

3.Put the measure into the Filters, set up show items when the value is 1.

4.The date of the slicer is from Date table.

When filtering from the slicer, it will show the expected data.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

your sample data does not include any values for yesterday.

Use a disconnected Dates table for the X axis and add 3/24 for the 8 hr shifting.

[Poojara_D12]

Hi @Sri_Anusha 

To achieve that:

Filter Data for 8 AM to 7:59 AM: Create a calculated column for "Shifted Date" in Power BI:

ShiftedDate = IF(HOUR('Table'[Date time]) >= 8, 'Table'[Date], 'Table'[Date] - 1)

• Use this column in your dropdown to ensure filtering works for 8 AM to 7:59 AM.

Default to Yesterday: Set the default dropdown value to Today() - 1 . You can do this by using a measure:

DefaultDate = TODAY() - 1

Charts X-Axis: Use the filtered data with "Shifted Date" and set the X-axis as a 24-hour period from 8 AM of the selected date to 7 AM of the next day. Ensure the axis uses the Date Time field and format it accordingly.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[FarhanJeelani]

Hi @Sri_Anusha ,

To achieve your goals in Power BI:

1. Filter Data for 8 AM to 7:59 AM:

You need to calculate and filter data dynamically for the selected date. Add a calculated column to determine whether a data point falls within the desired 24-hour range.

Calculated Column: InRange

InRange = 
VAR SelectedDate = SELECTEDVALUE('YourDateTable'[Date])
VAR StartTime = SelectedDate + TIME(8, 0, 0) 
VAR EndTime = SelectedDate + 1 + TIME(7, 59, 59) 
RETURN
    IF('YourTable'[DateTime] >= StartTime && 'YourTable'[DateTime] <= EndTime, 1, 0)

Apply a Filter:

• Add this column as a filter to your visualizations.
• Set the filter condition to InRange = 1.

2. Adjust the X-Axis to Show 8 AM to 7 AM:

You can customize the X-axis display using a calculated column for a shifted time period.

Calculated Column: ShiftedTime

ShiftedTime = 
VAR ShiftedHour = 
    IF(HOUR('YourTable'[DateTime]) >= 8, 
        'YourTable'[DateTime], 
        'YourTable'[DateTime] - 1)
RETURN 
    FORMAT(ShiftedHour, "hh:mm AM/PM")

• Use ShiftedTime as the X-axis for your charts to display the correct sequence of times from 8 AM to 7 AM.

3. Set the Default Date to Yesterday:

To default the selected date to yesterday in the dropdown, create a calculated column or use DAX logic to pre-filter the data:

Calculated Measure: Default Date

DefaultDate = TODAY() - 1

• Set the default filter or slicer to this value using Power BI's "Default Filter" option in the slicer properties.

4. Put It All Together:

• Slicer: Use the Default Date measure to pre-select yesterday in your slicer.
• Filters: Use the InRange column to restrict data to 8 AM to 7:59 AM of the selected date.
• X-Axis: Use the ShiftedTime column to display the time correctly from 8 AM to 7 AM.

Example Dataset After Transformation:

Date Sum of R DateTime Hours InRange ShiftedTime

09/30/2024	0.85	09/30/2024 8:21	8:00 AM	1	8:21 AM
09/30/2024	10.77	09/30/2024 18:44	6:00 PM	1	6:44 PM
10/01/2024	4.55	10/01/2024 0:54	12:00 AM	1	12:54 AM
10/01/2024	2.8	10/01/2024 7:59	7:00 AM	1	7:59 AM

This setup ensures that your data and visuals adhere to the required time period, default behavior, and X-axis formatting. Let me know if you need further clarifications!

Please accept this as solution if it helps. Appreciate Kudos.

[Sri_Anusha]

I am getting InRange as 0 for all instead of 1 in this method. Earlier also I used the same approach but I am getting 1. Could you share your pbix or guide me throught this. 

[FarhanJeelani]

Hi @Sri_Anusha,

If you're getting InRange = 0 for all rows, it likely indicates a mismatch in the calculated logic or the SELECTEDVALUE function not aligning with the slicer's value. Here's a step-by-step guide to troubleshoot and implement the solution correctly:

Updated Steps to Fix InRange Logic:

1. Ensure the Slicer is Bound to the Correct Date Column:

   • Confirm that your slicer is connected to the same column (Date) being used in the SELECTEDVALUE function in the InRange calculation.

2. Revised InRange Calculation: Replace the earlier DAX code with this revised version to account for potential mismatches:

   InRange = 
   VAR SelectedDate = SELECTEDVALUE('YourDateTable'[Date]) -- Value from the slicer
   VAR StartTime = SelectedDate + TIME(8, 0, 0)
   VAR EndTime = SelectedDate + 1 + TIME(7, 59, 59)
   RETURN
       IF(
           'YourTable'[DateTime] >= StartTime && 
           'YourTable'[DateTime] <= EndTime, 
           1, 
           0
       )

3. Test Without a Slicer (Hardcode a Date): To validate the logic, replace SELECTEDVALUE with a hardcoded date temporarily:

   InRange = 
   VAR SelectedDate = DATE(2024, 9, 30) -- Replace with any test date
   VAR StartTime = SelectedDate + TIME(8, 0, 0)
   VAR EndTime = SelectedDate + 1 + TIME(7, 59, 59)
   RETURN
       IF(
           'YourTable'[DateTime] >= StartTime && 
           'YourTable'[DateTime] <= EndTime, 
           1, 
           0
       )

   If this works (i.e., rows falling within the 8 AM to 7:59 AM range return 1), the issue is likely with how SELECTEDVALUE is retrieving the slicer's value.

4. Ensure DateTime Column is Properly Formatted:

   • Confirm that 'YourTable'[DateTime] is a valid datetime column and not stored as text.
   • If needed, create a calculated column to parse the text into a datetime format.

5. Set Default Date to Yesterday: Use this measure to default the slicer selection:

   DefaultDate = TODAY() - 1

   In the slicer's properties:

   • Set the slicer to Single Select mode.
   • Set the default selection to DefaultDate.

6. Validate Filters in the Visual: Ensure the visual is filtering correctly by checking the applied filters in the Filter Pane.

Troubleshooting Tips:

• Test the Date Range Logic: Manually filter the data table in Power BI to ensure rows between 8 AM and 7:59 AM are correctly identified.
• Check Time Zone Adjustments: Ensure your datetime values don't have offsets due to time zones.
• Share Sample Data: If issues persist, try sharing anonymized data to replicate the scenario.

This updated method should resolve the issue where InRange is incorrectly calculating all rows as 0. Let me know how it works for you!

[Sri_Anusha]

Yes , correct its working

 InRange =
VAR SelectedDate = FORMAT( CALCULATE( MAX( 'Exam Data'[FinalDateTime].[Date]), ALLSELECTED('Exam Data'[FinalDateTime].[Date] )), "mm/dd/yyyy")
VAR StartTime = SelectedDate + TIME(8, 0, 0)
VAR EndTime = SelectedDate + 1 + TIME(7, 59, 59)
RETURN
IF('Exam Data'[FinalDateTime] >= StartTime && 'Exam Data'[FinalDateTime] <= EndTime, 1, 0)

I am using this calculated column and using FinalDate in dropdown and when I select a particular date then it only gives me values from 8 AM to 11 PM for that particular selected and doesnt give hours for next day date till 7AM