How to handle two fact tables with different date range

Question

HiI have two separate PBI models, one for Sales and one for SalesBudget. Both has financial year September to August.&nbsp;In my Sales PBI model :I created the Fiscal Calendar using Calendarauto (8). So, the date range in Fiscal Calendar table referenced by sales data, from 2020-09-01 to 2024-08-31.&nbsp;In my SalesBudget PBI model :I also created the Fiscal Calendar using Calendarauto (8). So, the date range in Fiscal Calendar table referenced by budget data, from 2024-09-01 to 2028-08-31.&nbsp;Now I tried to integrate the SalesBudget data to the Sales PBI model (only SalesBudget data, not fiscal calendar table)Now I have two fact tables (Sales &amp; SalesBudget) in Sales PBI model.Both fact tables have connected to the Fiscal calendar table.But I couldn´t get any value from SalesBudget.I guess the problem lies on the different date range for the two fact tables. &nbsp;Since the Fiscal calendar data referenced by the Sales data, is that possible that the Fiscal calendar data also referenced by the SalesBudget data?If so, what/how can I do?If not, are there any other solutions on this issue?&nbsp;Thanks for all&nbsp;tips and advice.Regards Aixia

uzi2019 · Answer

Hi&nbsp;Aixia&nbsp;

&nbsp;
&nbsp;
Try the above modeling.&nbsp;create Fiscal Calendar with Sales date column.Make 1 to many relationship from fiscal cal to sales table and fiscal to sales budget table.
Connect both the tables with Date column.
&nbsp;
I hope it may resolves your issue!
&nbsp;

uzi2019 · Answer

Hi&nbsp;Aixia&nbsp;Try to change the relationship in cross filter direction to both.

&nbsp;
&nbsp;
I hope it helps!
&nbsp;

aixia · Answer

Hi&nbsp;Uzi2019&nbsp;Thanks for your quick reply.&nbsp;Below is the relationshops between them from my file.&nbsp; I think they have the conditions as you said?! But it didn´t work.&nbsp;&nbsp;&nbsp;Br/Aixia

aixia · Answer

Hi&nbsp;Uzi2019&nbsp;Thanks for your reply.&nbsp;When I changed the relationship to both, the value of Sales was gone. ie. no values from Sales and SalesBudget.RegardsAixia&nbsp;&nbsp;&nbsp;&nbsp;

aixia · Answer

HiHere comes more information about this interesting case:FactSales:Invoiced amount has been accrued to the correct financial year (202109-202408)BudgetSales: Total BudgetSales with different currency has been accrued to the correct financial year (202409-202808).Problems:There is relationship between the two fact tables: FactSales &amp; BudgetSales. But it didn´t work. I tried to change the relationship to both directions, it didn´t work neither.No Fiscal Year appears to the Budget visualizing.&nbsp;I guess problem came from the different date range for the two fact tables?!&nbsp;&nbsp;Please see attached files.&nbsp;https://www.dropbox.com/scl/fi/pzc36p8yie9fllx5nhzkm/Sales-budget-240423.pbix?rlkey=iv4uuk3gmdry46ug99feo3jrs&amp;st=qrmagalm&amp;dl=0&nbsp;https://www.dropbox.com/scl/fi/pzc36p8yie9fllx5nhzkm/Sales-budget-240423.pbix?rlkey=iv4uuk3gmdry46ug99feo3jrs&amp;st=jsg2gh9m&amp;dl=0&nbsp;https://www.dropbox.com/scl/fi/hs5p4pburyvoy5jh442xu/Sales-data.xlsx?rlkey=seffh9e69sj50htblzm5vs4q3&amp;st=5s2pusqy&amp;dl=0Need help:How to build the correct relationship between the two fact tables?The goal for this case is to show the Sales and BudgetSales volume on the same line chart.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Is that possible to using DAX or other solutions to achieve the goal?Thanks for any tips and advices.&nbsp; &nbsp; &nbsp; &nbsp; Regards&nbsp; &nbsp; &nbsp; &nbsp; Aixia

Forum Discussion

How to handle two fact tables with different date range

5 Replies

Related content

Handle AverageX on different granularities matrix drilldown

Handling Null Values

How do you handle semantic model name changes and schema differences during merges?

Different schema into different destination

Handle table with different amount of columns.