Median %

[Jorgast]

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%

[amitchandak]

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

[Jorgast]

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

[amitchandak]

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))  

[Jorgast]

@amitchandak 

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))

[amitchandak]

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

[Jorgast · Accepted solution]

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]

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]

@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.

[amitchandak]

Actually you need to test that