Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 tables , one contains actual sales from past 2 years, the other contains forecasted sales for the next 10 years (including the current year).
I have created a measure called Forecast_Volume:
=if([IsForecastValid],
CALCULATE(
SUM(Forecast[MC_5Y_Forecast]),
FILTER(ALL(Forecast[FYName]),
CONTAINS(VALUES('Calendar'[FYName]),'Calendar'[FYName],Forecast[FYName])
),
FILTER(ALL(Forecast[GOLDEN_ID]),
CONTAINS(VALUES(Golden_IDs[GOLDEN_ID]),Golden_IDs[GOLDEN_ID],Forecast[GOLDEN_ID])
),
FILTER(ALL(Forecast[PLANT]),
CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
)
),
BLANK()
)
(IsForecastValid tests the granularity of the calendar dimension in the pivot table, as the forecast is yearly and actual sales are daily.)
The table that contains the forecast data is called "Forecast".
MC_FY_Forecast = the volume forecast
FYName = the name of the Financial Year
Golden_ID = is the identifier of each product
PLANT = is the identifier of each factory
The date column in Master_Data table (which contains the actual sales) is connected to the date column in the Calendar table.
The FYName column in the Forecast is connected to a "joining table" which contians unique values of FYName, which in turn is connected to FYName in the calendar table. So, both Forecast and Master_Data tables are connected to the Calander either directly or indirectly.
My problem:
My measure is only showing data where the time periods in the forecast and Master_Data tables overlaps, ie. this year.
Any idea why this is happening? I do not have any slicers.
Many thanks, James
Solved! Go to Solution.
I changed:
CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
to
CONTAINS(VALUES(PLANTS[PLANT]),PLANTS[PLANT],Forecast[PLANT])
It appears that even though the MASTER_DATA and PLANTS tables are connected via the PLANTS column, it DOES matter which one you use in the DAX formula.
@jhd,
Please share sample data of your tables following the guide in this thread. Also we need to what DAX you use to calculate IsForecastValid measure.
Regards,
Lydia
I changed:
CONTAINS(VALUES(MASTER_DATA[PLANT]),MASTER_DATA[PLANT],Forecast[PLANT])
to
CONTAINS(VALUES(PLANTS[PLANT]),PLANTS[PLANT],Forecast[PLANT])
It appears that even though the MASTER_DATA and PLANTS tables are connected via the PLANTS column, it DOES matter which one you use in the DAX formula.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |