cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jorgast
Resolver II
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_YYYYTotal Prev_MonthChange% Change
Jan-18             700,000   
Feb-18             400,000             700,000        (300,000)-42.9%
Mar-18         1,000,000             400,000          600,000150.0%
Apr-18         1,300,000         1,000,000          300,00030.0%
May-18         1,400,000         1,300,000          100,0007.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,00011.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,00025.0%
Feb-19             800,000         1,000,000        (200,000)-20.0%
Mar-19             900,000             800,000          100,00012.5%
Apr-19             900,000             900,000                      -  0.0%
May-19         1,000,000             900,000          100,00011.1%
Jun-19         1,000,000         1,000,000                      -  0.0%
Jul-19         1,200,000         1,000,000          200,00020.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

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?
 

View solution in original post

9 REPLIES 9
amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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_YYYYTotal Prev_MonthChange% Change

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?
 

View solution in original post

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.

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

Actually you need to test that



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors