Forum Discussion
Nabil20_24
1 year agoHelper I
Multiple fact tables
Hi All, I have two tables: Sales Table (data available from 2023 to date): This contains the following columns: Sales ID Sales Category Created Date Value Close Date Budget Table: Th...
Kedar_Pande
1 year agoSuper User
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_241 year agoHelper I
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 monthlyDate 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