Forum Discussion
Resolver IIMedian %
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 column data
- 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% |
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
- amitchandak
Super User
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
JorgastI 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))
