Forum Discussion

katari_123's avatar
katari_123
Frequent Visitor
1 year ago
Solved

Help with Median

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'...
DAX
Median
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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.

v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
katari_123
Frequent Visitor
1 year ago

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's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.