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

[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