Forum Discussion
Jorgast
Resolver II
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 colu...
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?
JorgastResolver II
Resolver III 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
Super User
Super User12 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
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
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?