cancel
Showing results for
Did you mean:
Resolver II

## Median %

Hello BI Team

I am trying to determine the Median % change based on the previous 12 months.

1. The problem I am running into is all my data comparison points are measures and Median uses column data
2. I have also created a Summarize table and got the "Month and total" columns to populate but I am struggling with the previous month
•            Prev_Month = CALCULATE(sum(ZZTEST[Total]), PREVIOUSMONTH(ZZTEST[MMM_YYYY]))

How can i determine the Median % when the data is a measure?

In this example data set the Median from Nov-19 would be based on Nov-18 to Oct-19. This sample table is connected to a date table.

 MMM_YYYY Total Prev_Month Change % Change Jan-18 700,000 Feb-18 400,000 700,000 (300,000) -42.9% Mar-18 1,000,000 400,000 600,000 150.0% Apr-18 1,300,000 1,000,000 300,000 30.0% May-18 1,400,000 1,300,000 100,000 7.7% Jun-18 1,200,000 1,400,000 (200,000) -14.3% Jul-18 1,100,000 1,200,000 (100,000) -8.3% Aug-18 1,100,000 1,100,000 - 0.0% Sep-18 900,000 1,100,000 (200,000) -18.2% Oct-18 1,000,000 900,000 100,000 11.1% Nov-18 800,000 1,000,000 (200,000) -20.0% Dec-18 800,000 800,000 - 0.0% Jan-19 1,000,000 800,000 200,000 25.0% Feb-19 800,000 1,000,000 (200,000) -20.0% Mar-19 900,000 800,000 100,000 12.5% Apr-19 900,000 900,000 - 0.0% May-19 1,000,000 900,000 100,000 11.1% Jun-19 1,000,000 1,000,000 - 0.0% Jul-19 1,200,000 1,000,000 200,000 20.0% Aug-19 1,000,000 1,200,000 (200,000) -16.7% Sep-19 900,000 1,000,000 (100,000) -10.0% Oct-19 900,000 900,000 - 0.0% Nov-19 300,000 900,000 (600,000) -66.7% 300,000 (300,000) -100.0%
1 ACCEPTED SOLUTION
Resolver II

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?

9 REPLIES 9
Super User IV

Try something like this

``````Sum Sales = sum(sales[sales amount])

Rolling 12 Median = CALCULATE(MEDIANX(VALUES('Date'[Format Month]),[Sum Sales]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  ``````

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Proud to be a Super User!

Resolver II

I was able to get all the fields as columns in my Summary Table. Now i just need to figure how to calculate a rolling MEDIAN of prev 12m of % Change.

 MMM_YYYY Total Prev_Month Change % Change
Super User IV

12 before 12

``Rolling 12 Median before 12 = CALCULATE(MEDIANX(VALUES('Date'[Format Month]),[Sum Sales]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))  ``

Proud to be a Super User!

Resolver II

Why are you creating a measure for the sum of sales? I am trying to determine the % Change? what is "Date filer"

`Rolling 12 Median before 12 = CALCULATE(MEDIANX(VALUES('Date'[Format Month]),[Sum Sales]),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))`

Super User IV

Change % will not have date. It should be like

``(Rolling 12 Median -Rolling 12 Median before 12 )/(Rolling 12 Median before 12 )*100``

((Current - Prior)/Prior)*100

Proud to be a Super User!

Resolver II

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?

Community Support

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.
Resolver II

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.

Super User IV

Actually you need to test that

Proud to be a Super User!

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks