Report in Desktop vs. Service not behaving the same

[southgrad10]

I have a report that is capturing sales for the day by the hour.  For some reason, at 7pm CT, the report goes all zero and it immediately sets to the next day.  It doesn't behave like that in my Desktop version.  I assume that this is due to UTC in Service, but it doen't sound right to convert the OrderDate to UTC.  Why is this happening and how can I fix it?

[southgrad10]

My solution to this issue was to add a Date column to the table in SQL Server.  The Date was simply today's date in DATE format.  e.g. CAST(GETDATE() AS DATE).  I used that date instead of TODAY() in the measures.  It works beautifully.  It has been working great for a week now.

[123abc]

The behavior you're experiencing with your Power BI report in the Power BI Service (PowerBI.com) versus the Power BI Desktop could indeed be related to time zone differences, but it's important to consider a few factors when troubleshooting this issue:

1. **Time Zone Differences:** You mentioned that the issue occurs at 7 pm CT, which is Central Time (CT). Power BI Desktop typically uses the time zone settings of your local machine, whereas Power BI Service relies on its own server-side settings, which are usually in Coordinated Universal Time (UTC). If your data source contains date/time values without time zone information, this difference in time zones can affect how the data is displayed.

2. **Data Source Settings:** Check if your data source has any settings related to time zones or daylight saving time. Some databases or data sources store timestamps in UTC, while others may store them in local time. Ensure that your data source settings align with the time zone you intend to use.

3. **Date/Time Conversion:** While you mentioned that converting the OrderDate to UTC doesn't sound right, it might actually be necessary. Converting timestamps to a consistent time zone, like UTC, can help avoid time zone-related discrepancies. Power BI provides functions like `UTCNOW()` and `TODATETIMEZONE()` that can help with these conversions.

To potentially fix the issue:

1. **Data Transformation:** In Power BI Desktop, consider adding a calculated column to convert your OrderDate to UTC before importing the data into your model. This ensures that the data is consistently in UTC across both Power BI Desktop and Power BI Service.

Example DAX formula:
```
OrderDateUTC = TODATETIMEZONE([OrderDate], "UTC")
```

2. **Adjusting Time Zone Settings:** In Power BI Service, you can adjust the time zone settings for your report. Go to the dataset settings in Power BI Service and ensure that the time zone settings match your data source.

3. **Testing and Validation:** After making changes, thoroughly test your report in both Power BI Desktop and Power BI Service to ensure that the issue is resolved and that the data displays correctly.

4. **Consider Daylight Saving Time (DST):** Be aware of any daylight saving time changes in your time zone, as these can affect the behavior of your timestamps.

5. **Date/Time Visualization:** When creating visuals in Power BI, use date/time fields that have been converted to the desired time zone (e.g., UTC) to ensure consistent and accurate results.

By addressing these factors and ensuring that your date/time data is handled consistently between Power BI Desktop and Power BI Service, you should be able to resolve the issue of the report displaying zeros and transitioning to the next day at 7 pm CT.

[southgrad10]

Thank you for the quick and thorough response!  I will examine these options and let you know how it goes.  Thanks, again!