MEDIANX missing values for dates with no data

[DRA885]

Hello all.

I'm looking for some help on this DAX code:

Median Test 3 (next 3 months approved) = 
VAR MedianValueDiscipline =
    CALCULATE (
        MEDIANX (
            SUMMARIZE (
                'Actual & Forecast Project Resources',
                'Actual & Forecast Project Resources'[Work Week Ending Date],
                'Actual & Forecast Project Resources'[Capsule Descriptions.Discipline],
                "Median FTEs",
                    SUMX (
                        'Actual & Forecast Project Resources',
                        'Actual & Forecast Project Resources'[Consolidated FTEs]
                    )
            ),
            [Median FTEs]
        ),
        'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved",
        DATESINPERIOD (
            'Actual & Forecast Project Resources'[Work Week Ending Date],
            MAX ( 'Date'[EndOfThisWeek] ) - 1,
            3,
            MONTH
        )
    )
RETURN
    MedianValueDiscipline

This is producing the following results in a matrix:

Whilst this is giving the correct total value, the rows with null values are incorrect as the Median is only calculating for dates where there is a value. The rows with 'blanks' are from the field 'Actual & Forecast Project Resources'[Capsule Descriptions.Level Category]

My question is, how can I force zero values into the 'blanks' and obtain the correct result for each row of the matrix?

TIA, Ian

[Anonymous]

@DRA885 , did you manage to solve this? I am currently fasing the same problem.

[DRA885]

Sorry thought I had the solution but this is still incorrect.
Obviously now still looking for a solution to this

[DRA885]

solved with the following change to the Sum calculation of FTEs:

Median Test 3 (next 3 months approved) =

VAR MedianValueDiscipline =

    CALCULATE (

        MEDIANX (

            SUMMARIZE (

                'Actual & Forecast Project Resources',

                'Actual & Forecast Project Resources'[Work Week Ending Date],

                'Actual & Forecast Project Resources'[Capsule Descriptions.Discipline],

                "Median FTEs",

                    IF (

                        CALCULATE (

                            SUM ( 'Actual & Forecast Project Resources'[Consolidated FTEs] ),

                            'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved"

                        ) > 0,

                        CALCULATE (

                            SUM ( 'Actual & Forecast Project Resources'[Consolidated FTEs] ),

                            'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved"

                        ),

                        0

                    )

            ),

            [Median FTEs]

        ),

        DATESINPERIOD (

            'Actual & Forecast Project Resources'[Work Week Ending Date],

            MAX ( 'Date'[EndOfThisWeek] ) - 1,

            3,

            MONTH

        )

    )

RETURN

    MedianValueDiscipline