Median of the last 12 months

[Anonymous]

Hi all, 

I was wodering if anyone could help. I need to calcualte the median for the some of the last 12 months of a list of employees. 

The meadian formula in PBI, is based in a table column, so i tried to create a virtual table (with sumarise), which returns the table with the last 12 months total, to be used in the median formula, however when i create a real table to check the figures, it returns as blank the column of value. 

any idea how can i achive the median of the last 12 months? 

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

As @Greg_Deckler  said ,you could use MEDIANX() function . I just add a Slicer based on it:

Median Value Last 12 Months =
VAR _sele =
    MAX ( ForSlicer[Date] )
VAR _minDate =
    DATE ( YEAR ( _sele ) - 1, MONTH ( _sele ), DAY ( _sele ) )
RETURN
    MEDIANX (
        FILTER (
            EmployeeETable,
            'EmployeeETable'[Date] >= _minDate
                && 'EmployeeETable'[Date] <= _sele
        ),
        [Revenue]
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Anonymous]

Thanks @v-eqin-msft  it worked perfectly for my case. 

[Greg_Deckler]

@Anonymous - I'm with @Fowmy , without sample data it's really difficult to know. It might be as simple as:

Median = MEDIANX(FILTER('Table',[Date]>=DATE(YEAR(TODAY()-1),MONTH(TODAY()),DAY(TODAY()))),[Column])

[amitchandak]

@Anonymous , with a date table, try formula's similar to given in the example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Fowmy]

Hi @Anonymous 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn