Forum Discussion

Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
6 years ago
Solved

Median %

Hello BI Team   I am trying to determine the Median % change based on the previous 12 months.     The problem I am running into is all my data comparison points are measures and Median uses colu...
Need Help
  • Jorgast's avatar
    Jorgast
    6 years ago

    Now that I have everything as a column in my Summary table. I think this might work for me.

    Median = CALCULATE(MEDIAN(TEST[% Change]), DATESINPERIOD(TEST[MMM_YYYY],PREVIOUSMONTH(ZZTEST[MMM_YYYY]), -12, MONTH)).
    Will this get me a rolling 12 months of Medians?
     
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Jorgast 

It should be correct, except it returns median of 2018/12-2019/11 for the row 2019/11.

You could use the column to return median of 2018/11-2019/10 for the row 2019/11,

c =
CALCULATE (
    MEDIAN ( [change%] ),
    DATESINPERIOD ( 'date'[Date], EOMONTH ( MAX ( 'date'[Date] ), -1 ), -12, MONTH )
)

If you have any problem, please feel free to let me know.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
6 years ago

v-juanli-msft 

When I use that measure I get 0.60% for every month. It looks like the measure is trying to get the Median value for the entire data column of % Change. I need the Median value to be a rolling 12 months of historical % Change data.