Birthday display in Power BI with correct timezone (Web (Service) & App)

[Data_Owl2000]

How can I ensure that the birthday dates display correctly across Desktop, Web, and Mobile apps, considering timezone differences?
Especially when using filters and interactive elements, the dates sometimes shift by one day. What are best practices to handle this in Power BI?

The problem:

• In Power BI Desktop and on the Web Service, birthdays display correctly.

• But in the Power BI Mobile app or after refreshing the Web report, the birthday date sometimes shifts by one day (usually minus one day).

• I suspect this is due to timezone differences (UTC vs. local timezone).

[v-lgarikapat]

Hi @Data_Owl2000 ,

I wanted to follow up and confirm whether you’ve had a chance to review the information  shared by @jaineshp , @rohit1991  If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi 

[v-lgarikapat]

Hi @Data_Owl2000 ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @Data_Owl2000 ,

We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

Best Regards,

Lakshmi

[Data_Owl2000]

Hi jaineshp,
Thanks again for the thorough suggestions — I truly appreciate your persistence and depth here.

Unfortunately, I’ve tested the approaches you outlined (including the more aggressive ones), but the issue still remains:

• I tried the Manual Date Reconstruction in Power Query using #date([year], [Month], [Day]) — and set the type to Date— but the shifting still happens in Power BI Service.

• I also tested the DateTimeZone conversion method (switching to Date/Time/Timezone, then back to Date), but that didn’t resolve the issue either.

• Finally, I applied the DAX fix using DATEVALUE(FORMAT([Birthday], "yyyy-MM-dd")) hoping it would clean the timestamp fully — but the result still behaves inconsistently in the Service.

At this point, I suspect the root cause may lie deeper — possibly in how Power BI Service interprets source datetime values or applies implicit time zone shifts, especially if they're embedded in the original data.

If you have any additional ideas I can use to diagnose the source layer (even without full access), I’m open to trying it.

Thanks again — I really appreciate your support on this frustrating issue!

Best regards,
Data Owl

[jaineshp · Accepted solution]

Hey @Data_Owl2000,

Thank you for the feedback!

Pleasure to help with more detailed steps: -

Deep Diagnostic Steps

1. Trace the Exact Shift Pattern

• Create a simple table visual with these columns side by side:
  • Original Birthday column (as text)
  • Your fixed Birthday column
  • A calculated column: Shift_Days = DATEDIFF([Original_Birthday], [Fixed_Birthday], DAY)
• This will show you the exact shift pattern (always +1 day? -1 day? varies?)

2. Source Layer Investigation

Even without full database access, try these:

• Export a small sample (5-10 rows) from your source to CSV
• Open the CSV in Notepad - check if dates have hidden time components (like 2023-01-01 00:00:00.000)
• If possible, ask your data team to run: SELECT TOP 5 Birthday, CAST(Birthday AS VARCHAR(50)) FROM YourTable

Nuclear Solutions

3. Force UTC Conversion (Most Aggressive)

In Power Query, add this custom column:

= DateTime.From(DateTime.ToText([Birthday], "yyyy-MM-dd") & "T12:00:00Z")

Then convert to Date type. The "T12:00:00Z" forces noon UTC, preventing most timezone shifts.

4. Text-First Import Strategy

• Change your source query to explicitly cast dates as text: CAST(Birthday AS VARCHAR(10))
• Import as Text in Power Query
• Convert to Date using: = Date.FromText([Birthday_Text])

5. Gateway Timezone Override

If using an on-premises gateway:

• On the gateway machine, temporarily change Windows timezone to UTC
• Refresh your dataset
• Check if dates are correct
• Change timezone back
• This tests if the gateway is the culprit

Alternative Workarounds

6. Service-Side DAX Calculation

Create a calculated table in DAX that rebuilds your entire dataset:
CleanedData =
ADDCOLUMNS(
YourTable,
"Birthday_Clean",
DATE(YEAR([Birthday]), MONTH([Birthday]), DAY([Birthday]))
)

7. Incremental Refresh Investigation

If using incremental refresh:

• Temporarily disable it
• Do a full refresh
• Check if dates are still shifting

My top recommendation would be the "Force UTC Conversion" method (#3) - it's the most aggressive approach I've seen work for these deep-seated timezone issues. The key is forcing the time to noon UTC during import, which prevents most timezone interpretation problems.

Also, the diagnostic step (#1) is crucial - understanding the exact shift pattern will tell us if this is a consistent timezone offset or something more complex.

If none of these work, this might actually be a Power BI Service bug that needs Microsoft support, especially if the dates are perfect in Power BI Desktop but wrong in the Service.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer

[Data_Owl2000]

Hi Jainesh,

thank you very much for the detailed explanation – it really helps to understand the underlying issue around time zone conversion in Power BI Service.

In my case, I have already adjusted the data type in Power Query and ensured that the birthday column is stored as Dateonly (without any time or timezone component) – showing just the day, month, and year. Despite this, I'm still facing the issue where birthdays are displayed one day earlier in the Power BI Service compared to Power BI Desktop.

Do you have any other ideas what could cause this shift, even though the data type is now strictly Date?

Thanks again for your support!

Best regards,
Data Owl

[jaineshp]

Hi @Data_Owl2000 ,

That's really puzzling that you're still seeing the date shift even with Date-only format. Let me suggest a few additional checks:

First, verify at source level:

• In Power Query Editor, add a temporary column = [Birthday] to see if any hidden time component (like 00:00:00) is still there
• Check your raw data source - sometimes it passes datetime values even when we set it as Date

Model-level verification:

• In Model view, confirm Birthday column shows "Date" (not "Date/Time") in Properties
• Set Format to simple date like "dd/mm/yyyy"

Service-specific fix: The Power BI Service might have different regional settings than Desktop. Try:

• Settings in Power BI Service → Regional Settings and match it to your Desktop locale
• Create a simple table visual with just Birthday column and compare exact dates between Desktop and Service

DAX workaround:

Birthday_Fixed = DATE(YEAR([Birthday]), MONTH([Birthday]), DAY([Birthday]))

This strips any hidden timezone components completely.

Also, have you refreshed the dataset in Service after making the Power Query changes? Old datetime values might be cached.

Let me know if this helps!

Best Regards,
Jainesh Poojara | Power BI Developer

[Data_Owl2000]

Hi Jainesh Poojara,
Thanks a lot for the detailed suggestions and your time – I really appreciate your structured approach!

Unfortunately, I’ve tested all the methods you mentioned, and the issue still persists:

• In Power Query, I added a temporary Birthday column – it visually appears as date-only, but behind the scenes, Power BI still handles it as datetime.

• Regarding the raw source data, it appears that a time component is present, but I currently don’t have direct access to modify or control it

• In Model view, I’ve set the column type explicitly to Dateand used a simple format like dd-mm-yyyy– no effect.

• In Power BI, I matched the regional settings with Desktop, refreshed the dataset, and re-published – but the problem remains exactly the same. As for the web version, I couldn’t find any specific setting to change the regional configuration — neither via the profile menu nor through the File or Settings options.

• Even the DAX workaround: didn't fix it – it still ends up shifting the date

  Thanks again!

  Best regards,

  Data Owl

[jaineshp]

Hi @Data_Owl2000,

That's incredibly frustrating that the date shift persists! It sounds like a deep-seated issue with the raw data's time component. Since the usual methods aren't working, let's try some more aggressive, less common solutions.

Power Query: The Most Robust Fixes

• Manual Date Reconstruction: This is often the most reliable method for stubborn date shifts.

  1. In Power Query, select your Birthday column.

  2. Go to Add Column > Date and extract Year, Month, and Day into separate columns.

  3. Add a Custom Column using this formula:
     #date([Year], [Month], [Day])

  4. Set the data type of this new column to Date.
  5. Remove the original Birthday column and the temporary Year/Month/Day columns. Rename your new column to "Birthday".
• Why it works: This completely rebuilds the date from scratch, ignoring any hidden time components from the source.

• Explicit DateTimeZone Conversion (Advanced):

  1. In Power Query, change your Birthday column's data type to Date/Time/Timezone.

  2. Immediately change it back to Date.

  • Why it works: Sometimes, forcing Power Query to acknowledge potential time zone data first helps it strip it cleanly when converting to just a date. Ensure your regional settings in Power Query (under "File > Options and settings > Data source settings" or "Global options") match your system.

• DAX: Aggressive Text Conversion

  • Force to Text, Then to Date: This is a stronger DAX approach than just using DATE().
    
    Birthday_DAX_Fixed =
    DATEVALUE(FORMAT([Birthday], "yyyy-MM-dd"))
    
    

• Why it works: It converts your date to a pure text string (YYYY-MM-DD), which completely removes any time or time zone info, then converts that clean string back into a date.

Environment & Testing

• Check Gateway Settings: If you use an On-Premises Data Gateway, verify its regional settings on the machine where it's installed. The gateway can sometimes introduce shifts.

• Test with a New, Simple File: Create a brand new, minimal Power BI file. Import only the Birthday column, apply the Manual Date Reconstruction (first Power Query point), and publish it. This helps isolate if the issue is with your existing complex model.

• Crucial Step: After any change in Power Query or DAX, ensure you refresh the dataset in the Power BI Service and then check the dates in a simple table visual there.

Let me know which of these more robust methods you try and what the result is. This is a tough one, but we'll get it!

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer

[rohit1991]

Hi @Data_Owl2000 ,

This date shifting is almost always because of time zone conversion when Power BI handles DateTime fields. Here’s what I’ve found works best for birthdays and similar “date-only” fields:

1. Store birthdays as “Date” only (not DateTime): If your data source or Power Query gives you DateTime, convert it to Date right away (in Power Query, just change the type to Date, or use Date.From([Column])). This keeps Power BI from trying to adjust for time zones.

2. Avoid using UTCNOW, NOW, or DateTimeZone functions for birthdays: Those all return times in UTC, which can shift dates when viewed in Service or Mobile.

3. In your model, keep birthday columns as “Date”: Double check this in Power BI—should just be yyyy-mm-dd, not with a timestamp.

4. If you need to check for birthdays today, use:

IsBirthdayToday =
    IF(
        DAY([Birthday]) = DAY(TODAY()) &&
        MONTH([Birthday]) = MONTH(TODAY()),
        1, 0
    )

5. Mobile/Web tip: Stick with visuals that show only the date, not the full datetime. If your app or source absolutely requires DateTime, add a calculated column for the date part only and use that in your visuals.

[Data_Owl2000]

This line converts the column to a Date type, which is correct for handling birthdays in Power BI. This removes any time or time zone component and it still not correct 😞

= Table.TransformColumnTypes(#"Entfernte Spalten1",{{"BirthdayDate", type date}})

[Data_Owl2000]

Hi rohit1991,

thanks for summarizing the best practices so clearly – that’s exactly the approach I’ve been trying to follow.

I’ve already made sure that the birthday column is strictly set to Date(no time component) both in Power Query (type data) and in the model itself – just yyyy-mm-dd. I also avoided using any DataTimenow, UTCNOW, or NOW logic in relation to the birthday data.

Despite all of that, the dates still appear one day earlier in Power BI Service compared to Power BI Desktop. I’m not using any calculated time logic either – just the static birthday values.

Any further suggestions would be much appreciated – this one’s really persistent.

Thanks again!

Best regards,
Data Owl

[jaineshp]

Hi @Data_Owl2000,

Yes, you're correct — this issue is typically caused by time zone conversion (UTC vs. local time) during data refresh or report rendering.

Best Practices to Handle Birthday Dates in Power BI:

1. Store Dates as Date-Only (No Time):

   • In your source system or Power Query, ensure the Birthday column is strictly a Date type (yyyy-mm-dd) without a time component.

2. Use DateTimeZone.FixedUtcNow() in Power Query only when needed:

   • Avoid adding DateTimeZone logic to static date fields like birthdays.

3. Set Data Type to "Date" (not DateTime):

   • In Power BI Desktop:
     Model View → Birthday Column → Data Type → Date

   • This tells Power BI not to apply timezone shifts.

4. Avoid Using UTCNow() or Now() for Date Comparison Against Birthdays:

   • These return values in UTC and may cause a shift when viewed in different regions.

5. For Dynamic Date Logic (like upcoming birthdays), use Local Offset Logic:
   
   Birthday_Local = DATE(YEAR(TODAY()), MONTH([Birthday]), DAY([Birthday]))
   
   

6. Mobile & Web Workaround:

   • Use visuals that display only the date part and avoid datetime formats.

   • Use DAX to convert datetimes to local dates if needed.

By ensuring birthdays are treated as pure dates and avoiding UTC-based datetime calculations, you can maintain consistency across Desktop, Web, and Mobile views.

I am sure this would definitely help.

Best Regards,
Jainesh Poojara | Power BI Developer