Forum Discussion
Help with Median
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.
Community SupportHello 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?- 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.
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
