Help with Median

[katari_123]

Greetings! I've been trying to figure out how to get a 3-month median, but I'm stumped. 

Here's my sample data set. Basically, for 6/2025 MONTH_END, I'd expect the value to display 157 since that's the median for date ranges 4/2025 - 6/2025.

Best I've done is:

R3M_MEDIAN_VALUE =

CALCULATE(

    MEDIANX('R3M',[VALUE]),

    DATESINPERIOD(R3M[MONTH_END], MAX(R3M[MONTH_END]), -3, MONTH)

    )

But it's probably not filtering correctly.

Really appreciate any help. Thanks in advance! 🙂 

[v-ssriganesh · Accepted solution]

Hello @katari_123,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your scenario in Power BI and achieved the expected 3-month rolling median result. For example, for the MONTH_END of June 2025, the median correctly calculates as 157, using the values from April to June 2025 exactly as you described.

What I did:

• Created sample data for MONTH_END and VALUE.
• Built a separate Date table and linked it to the MONTH_END column.
• Used the following DAX measure to calculate the 3-month rolling median:

R3M Median Value =

VAR CurrentDate = MAX ( 'Date'[Date] )

VAR Period =

    DATESINPERIOD (

        'Date'[Date],

        CurrentDate,

        -3,

        MONTH

    )

RETURN

CALCULATE (

    MEDIAN ( R3M[VALUE] ),

    FILTER (

        ALL ( 'Date' ),

        'Date'[Date] IN Period

    )

)

Expected output:

I’m attaching the .pbix file for your reference so you can explore the full solution and adapt it to your dataset.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[katari_123]

Thanks so much for your thorough explanation! The pbix file was also very helpful. I was able to apply it to my data set. 

I'm fairly new to PBI. Can you help me understand why this only works with a separate Date table? 

[v-ssriganesh]

Hello @katari_123,

You're very welcome I'm glad it helped you get the expected results.

DAX time functions like DATESINPERIOD() only work correctly with a complete and continuous date range something your fact table (like R3M) usually doesn't have. Fact tables often skip dates or only include months where there’s data.

A Date table ensures:

• All dates are present (no gaps).
• Proper time-based calculations like rolling medians.
• Functions like DATESINPERIOD can go back the full 3 months, even if your data is missing some months.

Without it, DAX can't calculate accurate rolling windows. That's why the Date table is essential for reliable results.

[katari_123]

Sorry to bug you again. I tried to apply it to a larger data set with multiple users and it seemed to change the relationship because the R3M table now has duplicate dates. It's probably a simple fix, but would you be able to help me figure it out? 🙂

RollingMedian 

[v-ssriganesh]

Hello @katari_123,
Thanks for sharing your .pbix file and additional details.

I’ve reproduced your scenario using the same dataset you provided, and the 3-month rolling median per USER_ID is now calculating correctly as expected.

For example:

• For USER_ID = 12345, MONTH_END = 01 June 2025, the rolling values are April (206), May (157), June (143) → Median = 157
• Similar accurate results are appearing for all other users as well.

I’m attaching the working .pbix file for your reference so you can see the final setup and DAX formula that delivers the correct output.

[techies]

Hi @katari_123 please try this

R3M_MEDIAN_ =

VAR CurrentDate = MAX('date'[endm])

VAR StartDate = EDATE(CurrentDate, -2)

VAR CurrentUser = MAX(R3M[USER_ID])

RETURN

    CALCULATE(

        MEDIANX(

            FILTER(

                R3M,

                R3M[USER_ID] = CurrentUser &&

                R3M[MONTH_END] >= StartDate &&

                R3M[MONTH_END] <= CurrentDate

            ),

            R3M[VALUE]

        )

    )

[katari_123]

Thanks for your response! I tested it out, but it returned the same result where the Median value matches the actual value.

I did the following -- create a separate date table, linked date and main tables using date fields.