Multiple fact tables

[Nabil20_24]

Hi All,

I have two tables:

1. Sales Table (data available from 2023 to date): This contains the following columns: 

   • Sales ID
   • Sales Category
   • Created Date
   • Value
   • Close Date

2. Budget Table: This includes the budget for each sales category for the year 2024. Each sales category (e.g., Electronics) is repeated for each month (12 times total) with the following columns:

   • Sales Category
   • Date (01/01/2024, 01/02/2024,/01/03/2024 etc)
   • Budget Value

I have already created a Date Table and established a one-to-many relationship between the Sales Table  and the Date Table. and also created a one to many relationship between budget and sales table, one two many relationship between budget and datatable (inactive)
Now I want to create a visual table that shows the flowing: 

• The date from the Date Table
• Year-to-date (YTD) sales from the Sales Table
• Monthly budget figures from the Budget Table for each sales category.
  but the YTD sales are either empty or inccorect  how can I do can I join the two table, to see the budget, sales and also the date I want the date to be used from the datatable created.
  
  The current data modeling works mentioned earlier works well, but the budget values only display up until September because I created the date table using the max and min dates from the sales table. When I extend the date range in the date table to December 2024, it affects the PYTD sales calculation, causing it to sum sales for all months in FY2023 instead of just January to September. Since I only have data available up to September for 2024, I want to ensure a proper comparison (apples to apples) between the two years
   

[Ashish_Mathur]

Hi,

There should not be any relationship between budget and sales.  Active the inactive relationship.  Crate a Category Dim table.  Ensure that the calendar table goes to the last date available in the budget table.  This measure pattern should work

Rev = sum(Data[Revenue])

YTD rev = calculate([Rev],datesytd(calendar[date],"31/12"))

Hope this helps.

[Kedar_Pande]

Modify your Date Table formula to extend the range-

DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31))

Define your YTD Sales measure as follows:

YTD Sales = 
CALCULATE(
SUM(SalesTable[Value]),
DATESYTD(
'DateTable'[Date], 
"12/31"
),
SalesTable[Created Date] <= TODAY() // This ensures only sales up to today are included
)

Create a Monthly Budget measure:

Monthly Budget = 
CALCULATE(
SUM(BudgetTable[Budget Value]),
USERELATIONSHIP('DateTable'[Date], BudgetTable[Date]), // Activate the inactive relationship
BudgetTable[Sales Category] = SalesTable[Sales Category]
)

PYTD Sales measure-

PYTD Sales = 
CALCULATE(
SUM(SalesTable[Value]),
SAMEPERIODLASTYEAR(
DATESYTD(
'DateTable'[Date], 
"12/31"
)
),
SalesTable[Created Date] <= EDATE(TODAY(), -12) // Restrict PYTD sales to the same period as current year
)

[Nabil20_24]

hank you for this information! The issue seems to be that the visual still displays data for September. Here’s how my table is set up: I’m using a date from the date table, a date from the budget table, and a sales category. Could this be due to the differing granularities between the date table and the budget table?

FYI the datetable neds to be daily as the sales is daily but the budget is monthly 

Date	DateColumn	Sales category	Target
06/02/2024 00:00	01/02/2024 00:00	Electronics	0
26/02/2024 00:00	01/02/2024 00:00	Electronics	0
31/05/2024 00:00	01/05/2024 00:00	Electronics	0
10/07/2024 00:00	01/07/2024 00:00	Electronics	0
19/07/2024 00:00	01/07/2024 00:00	Electronics	0
07/08/2024 00:00	01/08/2024 00:00	Electronics	0
13/08/2024 00:00	01/08/2024 00:00	Electronics	0
16/08/2024 00:00	01/08/2024 00:00	Electronics	0
19/09/2024 00:00	01/09/2024 00:00	Electronics	0
26/09/2024 00:00	01/09/2024 00:00	Electronics	0
16/04/2024 00:00	01/04/2024 00:00	Electronics	256
29/04/2024 00:00	01/04/2024 00:00	Electronics	177
30/04/2024 00:00	01/04/2024 00:00	Electronics	123

[elitesmitpatel]

The issue arises because your DateTable is limited by the minimum and maximum dates in your SalesTable. Since you only have data up to September 2024, extending the DateTable to December 2024 causes the PYTD calculation to include data from the entire year 2023, which is incorrect.

Solution:

Create a Separate Date Table:

Create a new table in Power BI using the CALENDAR function to generate a complete date range from a start date to an end date. This will ensure you have all the necessary dates for your analysis, regardless of the data in your SalesTable.
Set the start date to the beginning of the year you want to analyze (e.g., 01/01/2023) and the end date to a future date (e.g., 31/12/2024).
Adjust Relationships:

Maintain the one-to-many relationship between the SalesTable and the DateTable based on the date columns in both tables.
Remove the relationship between the BudgetTable and the DateTable. This will prevent the DateTable's filter context from affecting the BudgetTable calculations.
Create a DAX Measure for YTD Sales:

Code snippet
YTD Sales =
CALCULATE(
SUM(SalesTable[Value]),
FILTER(
ALL('DateTable'),
'DateTable'[Date] <= MAX('DateTable'[Date])
)
)

please Kudos the work if it helps Accept it as Solution.