Calculating the SUM of the Median values, within a set period bracket.

[Robindebr]

I want to get the cumulative values of the median, but I struggle to get it properly visualized.

I have the following Measure: 

netto_median_intake =

VAR SelectedMonth = SELECTEDVALUE(DateTabletest[Month Number])

VAR medpos =

    CALCULATE(

        MEDIANX(

            FILTER(

                ALL(DateTabletest),

                DateTabletest[Month Number] = SelectedMonth &&

                DateTabletest[Year] IN {2021, 2022, 2023, 2024}

            ),

            DateTabletest[intake_numbers]

        )

    )

VAR medneg =

    -1 * CALCULATE(

        MEDIANX(

            FILTER(

                ALL(DateTabletest),

                DateTabletest[Month Number] = SelectedMonth &&

                DateTabletest[Year] IN {2021, 2022, 2023, 2024}

            ),

            DateTabletest[accept_numbers]

        )

    )

VAR mednet = medpos + medneg

RETURN

    IF(

        SELECTEDVALUE(DateTabletest[Month Number]) = SelectedMonth &&

        SELECTEDVALUE(DateTabletest[Year]) = 2025,

        mednet,

        BLANK()

    )

What I want is to get a future prospect -: So I have the current intake numbers for 06-2025 and for 07-2025 it is currentintake + nettomedian-07. For 08 it's gonna be currentintake + nettomed07 + nettomed08. To do that I thought up the following measure:

intake =

VAR currentmonth = MONTH(TODAY())

VAR currentyear = YEAR(TODAY())

VAR selectedmonth = SELECTEDVALUE(DateTabletest[Month Number])

VAR selectedyear = SELECTEDVALUE(DateTabletest[Year])

VAR countintake=

    CALCULATE(

        COUNTROWS(----private table info ----),

        FILTER(  -----private table info ----

        )

    )

VAR mediaan =

    CALCULATE(

        DateTabletest[netto_median_intake],

        FILTER(

            DateTabletest,

            DateTabletest[Year] = 2025 &&

            DateTabletest[Month Number] <= currentmonth &&

            DateTabletest[Month Number] >= selectedmonth

        )

)

RETURN

    SWITCH(

        TRUE(),

        selectedmonth < currentmonth || selectedyear < currentyear, BLANK(),

        selectedmonth = currentmonth && selectedyear = currentyear, countintake ,

        selectedyear = currentyear, countintake + mediaan,

        BLANK()

    )

Except it isn't returning what I would expect. It's only returning the currentintake value. the Median values are somehow 0. However in a table visual with nettomedian does visualize the values properly with their month-year column. Also hardcoding the month values (6, 7,8) doesn't seem to make a difference. 

[Elena_Kalina]

Hi @Robindebr 

You might want to create a separate measure for the cumulative calculation

CumulativeNetMedian = 
VAR CurrentMonthNum = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR SelectedMonthNum = SELECTEDVALUE(DateTabletest[Month Number])
VAR SelectedYear = SELECTEDVALUE(DateTabletest[Year])

RETURN
    IF(
        SelectedYear = CurrentYear && SelectedMonthNum >= CurrentMonthNum,
        SUMX(
            FILTER(
                ALL(DateTabletest),
                DateTabletest[Year] = CurrentYear &&
                DateTabletest[Month Number] >= CurrentMonthNum &&
                DateTabletest[Month Number] <= SelectedMonthNum
            ),
            [netto_median_intake]
        ),
        BLANK()
    )

Then your intake measure would simply be

intake =
VAR countintake = [Your count measure]
VAR cummedian = [CumulativeNetMedian]
RETURN countintake + cummedian

[v-priyankata]

Hi @Robindebr 
@DataNinja777 Thank you for your inputs.

I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

[DataNinja777]

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,