Forum Discussion
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's the median for date ranges 4/2025 - 6/2025.
Best I've done is:
But it's probably not filtering correctly.
Really appreciate any help. Thanks in advance! 🙂
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.
7 Replies
Hi katari_123 please try this
R3M_MEDIAN_ =VAR CurrentDate = MAX('date'[endm])VAR StartDate = EDATE(CurrentDate, -2)VAR CurrentUser = MAX(R3M[USER_ID])RETURNCALCULATE(MEDIANX(FILTER(R3M,R3M[USER_ID] = CurrentUser &&R3M[MONTH_END] >= StartDate &&R3M[MONTH_END] <= CurrentDate),R3M[VALUE]))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.
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.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?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.
