Forum Discussion

Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
6 years ago
Solved

Median %

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...
Need Help
  • Jorgast's avatar
    Jorgast
    6 years ago

    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?
     
Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
6 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Jorgast
    Icon for Resolver II rankResolver II
    6 years ago

    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?
     
    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Actually you need to test that

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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's avatar
        Jorgast
        Icon for Resolver II rankResolver II
        6 years ago

        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.