How to handle two fact tables with different date range

[Aixia]

Hi

I have two separate PBI models, one for Sales and one for SalesBudget. Both has financial year September to August.

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.

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.

Now I tried to integrate the SalesBudget data to the Sales PBI model (only SalesBudget data, not fiscal calendar table)

1. Now I have two fact tables (Sales & SalesBudget) in Sales PBI model.
2. Both fact tables have connected to the Fiscal calendar table.
3. But I couldn´t get any value from SalesBudget.

I guess the problem lies on the different date range for the two fact tables.  

1. Since the Fiscal calendar data referenced by the Sales data, is that possible that the Fiscal calendar data also referenced by the SalesBudget data?
2. If so, what/how can I do?
3. If not, are there any other solutions on this issue? 

Thanks for all tips and advice.

Regards Aixia

[Aixia]

Hi

Here 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:

1. There is relationship between the two fact tables: FactSales & BudgetSales. But it didn´t work. I tried to change the relationship to both directions, it didn´t work neither.
2. 
3. No Fiscal Year appears to the Budget visualizing. 
4. 

   I guess problem came from the different date range for the two fact tables?!

5. Please see attached files. 

   https://www.dropbox.com/scl/fi/pzc36p8yie9fllx5nhzkm/Sales-budget-240423.pbix?rlkey=iv4uuk3gmdry46ug...

   https://www.dropbox.com/scl/fi/pzc36p8yie9fllx5nhzkm/Sales-budget-240423.pbix?rlkey=iv4uuk3gmdry46ug...

   https://www.dropbox.com/scl/fi/hs5p4pburyvoy5jh442xu/Sales-data.xlsx?rlkey=seffh9e69sj50htblzm5vs4q3...

6. Need 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.

               Is that possible to using DAX or other solutions to achieve the goal?

   Thanks for any tips and advices.

        Regards

        Aixia

[Uzi2019]

Hi @Aixia 

Try the above modeling. 
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.

I hope it may resolves your issue!

[Aixia]

Hi @Uzi2019 

Thanks for your quick reply. 

Below is the relationshops between them from my file.  I think they have the conditions as you said?! But it didn´t work. 

Br/Aixia

[Uzi2019]

Hi @Aixia 
Try to change the relationship in cross filter direction to both.

I hope it helps!

[Aixia]

Hi @Uzi2019 

Thanks for your reply. 

When I changed the relationship to both, the value of Sales was gone. ie. no values from Sales and SalesBudget.

Regards

Aixia