How to find a Median over Months

[Sam07]

Hello everyone,

I need to develop a metric that helps me finding the Median across months. For instance, in January, the metric has to return the median of January's values. But in February, the metric has to make a list of the values ​​for January and February and bring the median accordingly the values of these two months. And so on for the other months. In March, the metric has to find the median according to the values ​​of January, February and March.

This is the formula I'm currently using:

Median({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VRMBDAFLG_LOT_APPROVED={'1'},VRMBDAFLG_OUTLIER={'1'}>}VRMBDADAYS_TO_BE_RELEASED)

My sincere thanks in advance

[Clement15]

Hello,

In what type of graph do you want to use this measure?

[Sam07]

@Clement15  thank you for willing to help me. In a line chart and a KPI. In addition, in the line chart I'm using months for the X axis.

[Clement15]

Hello, For the KPi I use this formula:

{<Date={">$(=YearStart(max(Date)))) <$(=max(Date))"},[YearMonth]=,[Year]=,Date=,Month=>}median(aggr(sum(Value),[YearMonth]))

It allows you to have the median as you want over the year, the set analysis allows you to make the right selection by just selecting the max date you want. Here, YearMonth is created using ' MonthName(Date) as YearMonth ' in the script.

For the graph, I used this in measure:

RangeFractile(0.5, above( sum(Value),0,12))

and YearMonth in dimension

The problem is that if you have several years, it will not be limited to the beginning of it.

These formulas are examples, you have to adapt them to your data

[Sam07]

Thank you for helping me, @Clement15 . I'm going to try again using the formula you shared with me. 

I just would like to check with you this formula:

{<Date={">$(=YearStart(max(Date)))) <$(=max(Date))"},[YearMonth]=,[Year]=,Date=,Month=>}median(aggr(sum(Value),[YearMonth]))

It seems that the beginning of it is missing.

[Clement15]

What do you want to check? The formula is correct on my side. Just adapt it with your measure

[Sam07]

Okay, my mistake. Thank you.