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

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

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

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