Rolling median not same as that in Excel

[sgupta22]

Hi,

I am trying to calculate the rolling median of a measure using the following formula:

Median EBIT margin = CALCULATE(MEDIANX('All Measures', [EBIT margin]), FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))

However, this formula gives me results which are different from those calculated in MS Excel using the same underlying raw data (from the 3rd row onwards).  Below, I am giving the actual [EBIT margin] values along with the Median values that I get using Power BI and Excel. Would really appreciate if someone can point out what I am doing wrong or point me in the right direction.

Thank you very much!

Shashank

Date	Actual EBIT margin	Median using Excel	Median using Power BI
2010	5.7%	5.7%	5.7%
2011	7.2%	6.4%	6.4%
2012	11.7%	7.2%	8.2%
2013	8.6%	7.9%	8.3%
2014	13.7%	8.6%	9.5%
2015	9.7%	9.1%	9.6%
2016	10.8%	9.7%	9.8%
2017	15.7%	10.3%	10.8%
2018	14.5%	10.8%	11.3%

@Ashish_Mathur 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  Reapply the conditions in the slicers.

Hope this helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sgupta22]

Thanks for your reply Ashish.

Strangely, it's not working in my file (getting blank) even though I can see you are getting the right values.  There must be something wrong in my file.  I am attaching it here if that helps.

Thank you very much for your help.  Much appreicated. 

Regards

Shashank

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  Reapply the conditions in the slicers.

Hope this helps.

[sgupta22]

This works! Thanks a ton!! You are a genius.

Just one more question if I can indulge you.  Is there a way the start date can be dynamic?  For example, it should start with the minimum date at which the [EBIT margin] is not "blank" or "0"?

Thanks a ton again.

Cheers

Shashank  

[Ashish_Mathur · Accepted solution]

You are welcome.  Thank you for your kind words.  Please download the revised file from here.

Hope this helps.

[sgupta22]

This is amazing 😊  Thank you again so much!

[Ashish_Mathur]

You are welcome.

[johnt75]

You're not doing the iteration over the years, try

Median EBIT =
CALCULATE (
    MEDIANX ( VALUES ( 'Calendar'[Year] ), [EBIT] ),
    REMOVEFILTERS ( 'Calendar' ),
    'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)

[sgupta22]

Thank you johnt75 for your reply.

Unfortunately, when I use the above formula, I don't get any values for the Median EBIT.  It just becomes blank.

[johnt75]

Hmm, it worked for me. 

Are you using the Calendar[Year] column in the table visual? What is the defintion of the [EBIT] measure ?

[sgupta22]

Yes, I am... Actually the measure is EBIT margin and it's formula is below:

EBIT margin =
    VAR Revenue = SUM(Data[Total Revenue])
    VAR EBIT = [Adjusted EBIT]
RETURN
    IF(Revenue = 0, BLANK(), EBIT / Revenue)