Time Intelligence Functions Not Working After Changing Database Source in Advanced Editor

[fayzy100]

Hello Community,

I’m facing a recurring issue in Power BI related to Time Intelligence DAX functions after modifying the data source.

Issue Summary

When I change the database connection using the Advanced Editor (for example, switching from a test database to a production database), all my Time Intelligence functions — such as TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD, etc.stop working properly.

After the change, measures that used to work fine now return blank values or incorrect calculations.

---

What I’ve Tried

Verified that the new database has the same schema and data types
Checked that the Date Table is marked as a Date Table
Ensured all relationships are active and valid
Refreshed the entire model and data
Recreated measures and checked for syntax issues — still the same result

---

Observation

It seems that when the data source is changed via the Advanced Editor, Power BI may lose internal column bindings (lineage IDs), especially for date fields used in Time Intelligence.

Although the Date Table and relationships appear visually intact, the engine might not recognize them properly for date-based calculations anymore.

Expected Behavior

Time Intelligence functions should continue to work after changing the data source if the schema, relationships, and table names remain the same.

Workarounds Tried / Suggested

1. Re-marked the Date Table under Model View → Mark as Date Table.

2. Deleted and recreated the relationship between the Date Table and fact tables.

3. Verified the date column lineage in Power Query to ensure consistency.

4. Instead of editing in Advanced Editor, tried using File → Options and Settings → Data Source Settings → Change Source... (this seems more stable).

---

 

Has anyone experienced similar behavior or found a reliable way to change a data source without breaking Time Intelligence measures?
Would appreciate any insights, official documentation, or confirmed fixes from the Microsoft team.

---

#powerBI #PowerQuery  #timeIntelligence

[Poojara_D12]

Hi @fayzy100 

Yes as per my understanding, what you’re describing is a known and recurring behavior in Power BI, particularly when the data source connection is manually altered through the Advanced Editor. Even though the schema and table names remain identical, the internal lineage IDs (the hidden identifiers Power BI uses to map model relationships and metadata) can break silently during this process. These lineage IDs are crucial for DAX Time Intelligence functions, because Power BI relies on them to understand which date column is being used for filtering and which tables are linked to it.

When you change the connection string directly in the M code via the Advanced Editor, Power BI essentially treats it as a new query instance, even if the table name and structure are identical. This disrupts the lineage between your Date table and related fact tables. As a result, functions like TOTALYTD, SAMEPERIODLASTYEAR, or DATESYTD lose the internal reference that tells them which column represents “Date,” leading to blank or incorrect results.

Your observations and troubleshooting steps are spot-on. The most reliable and supported way to change the data source — while preserving lineage — is to use File → Options and Settings → Data Source Settings → Change Source.... This approach updates the connection at the metadata level without regenerating lineage IDs, so the semantic model and relationships remain intact. If you must modify the M query directly, a workaround is to copy the new connection details (server/database names) into the existing source step without deleting or reloading the query, ensuring Power BI doesn’t treat it as a new object.

[raisurrahman]

@fayzy100: 

Thanks to @AnalyticPulse and @v-dineshya for their valuable insights.

As far as I know, there are two ways to resolve this:

      1. Use parameters to switch between data sources.
      2. Create connection queries for each data source, then reference connection queries wherever needed.

It looks like you’re loading the data directly in Power BI; you should load it through Power Query instead.

[AnalyticPulse]

hi, @fayzy100 
when i had multiple data sources in my project i used parameters for the database name, i never faced any issue, we used to change dadtasource dynamically from parameters without touching the source code. when you change the datasource powerbi will change the internal column ids, even though your columns have similar names their ordering ids will be different, can you confirm which data source you were using in dev and what is in prod?

My Work:
Analytic Pulse Blog 
Docynx Productivity Tools 

[v-dineshya]

Hi @fayzy100 ,

Thank you for reaching out to the Microsoft Community Forum.

Changing the data source via the Advanced Editor in Power BI can disrupt Time Intelligence DAX functions, even when the schema and relationships appear unchanged. Time Intelligence functions breaking after changing the data source (especially via Advanced Editor) due to lineage loss.

Please try below options to fix the issue.

1. Extract the source steps into a separate query. Create a new query for the new source. Point your main query to the new source query. This minimizes disruption to transformations and lineage.

2. Check the Date column is of type Date not DateTime, and that it’s consistently named and formatted across sources.

3. Instead of editing queries directly, In Power Query --> Manage Parameters --> create: ServerName and DatabaseName. Modify your source step.

Source = Sql.Database(ServerName, DatabaseName)

Note: when switching between test/production, change parameter values only, lineage remains intact.

Please refer below links.

Solved: Changing data source without effecting all query s... - Microsoft Fabric Community

Solved: Re: Time Intelligence Functions not working - what... - Microsoft Fabric Community

Solved: Dataflow Workspace lineage keeps old Data source i... - Microsoft Fabric Community

Solved: Change Data Lineage from Web Interface - Microsoft Fabric Community

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @fayzy100 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @fayzy100 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh