Hi @Robindebr ,
The issue you're encountering stems from how filter context is handled within your intake measure. Your netto_median_intake measure is designed to work correctly when it has context for a single, specific month, as it relies on SELECTEDVALUE(DateTabletest[Month Number]) to identify that month. However, in your intake measure, the mediaan variable attempts to use CALCULATE with a FILTER that spans multiple months. When the filter context contains more than one month, SELECTEDVALUE returns a BLANK(), which causes your netto_median_intake measure to fail and not produce the value you expect for your cumulative calculation.
To solve this, you must iterate through each future month one by one, calculate the median value for each, and then sum the results. The appropriate DAX pattern for this is to use an iterator function like SUMX, which establishes a row context for each month in the period you are calculating over. This allows your original netto_median_intake measure to evaluate correctly for each individual month before the final summation.
Here is the revised intake measure incorporating this logic:
intake =
VAR currentmonth =
MONTH ( TODAY () )
VAR currentyear =
YEAR ( TODAY () )
VAR selectedmonth =
SELECTEDVALUE ( DateTabletest[Month Number] )
VAR selectedyear =
SELECTEDVALUE ( DateTabletest[Year] ) -- This calculates the actual intake for the current month.
-- It's important to use ALL(DateTabletest) or the relevant table
-- to ensure it always calculates for the *current* month, ignoring the filter from the visual.
VAR countintake =
CALCULATE (
-- Note: Replace 'YourTableName' with your actual table.
COUNTROWS ( 'YourTableName' ),
FILTER (
ALL ( DateTabletest ),
-- Or your main data table
DateTabletest[Year] = currentyear
&& DateTabletest[Month Number] = currentmonth -- Add other necessary filters from your original 'countintake' here
)
) -- This SUMX iterates through each month between the current month and the selected month,
-- calculates the median for each one, and then sums them up.
VAR cumulative_median =
SUMX (
FILTER (
ALL ( DateTabletest ),
DateTabletest[Year] = selectedyear
&& DateTabletest[Month Number] > currentmonth
&& DateTableTabletest[Month Number] <= selectedmonth
),
[netto_median_intake]
)
RETURN
SWITCH (
TRUE (),
-- For any date before the current month, return blank.
selectedyear < currentyear
|| ( selectedyear = currentyear
&& selectedmonth < currentmonth ), BLANK (),
-- For the current month, return just the actual intake.
selectedyear = currentyear
&& selectedmonth = currentmonth, countintake,
-- For future months in the current year, return the actual intake plus the cumulative forecast.
selectedyear = currentyear
&& selectedmonth > currentmonth, countintake + cumulative_median,
-- Default case for all other scenarios (e.g., future years).
BLANK ()
)
This corrected code works by first calculating a stable countintake value that is fixed to the actual current month, ignoring the context from the visual. Then, the cumulative_median variable uses SUMX to create a table of future months to be forecasted. SUMX evaluates the [netto_median_intake] measure for each row (each month) in that table, ensuring SELECTEDVALUE receives the single month context it needs. Finally, the SWITCH statement cleanly handles the logic to return the historical intake for the current month and the cumulative forecast for all future months, providing the accurate visualization you need.
Best regards,
