Forum Discussion

DRA885's avatar
DRA885
Frequent Visitor
3 years ago

MEDIANX missing values for dates with no data

Hello all.   I'm looking for some help on this DAX code:     Median Test 3 (next 3 months approved) = VAR MedianValueDiscipline = CALCULATE ( MEDIANX ( SUMMARIZE ( ...
DRA885's avatar
DRA885
Frequent Visitor
3 years ago

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