cancel
Showing results for
Search instead for
Did you mean:
Resolver II

## Median of the last 12 months

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?

1 ACCEPTED SOLUTION
Community Support

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

5 REPLIES 5
Community Support

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

Resolver II

Thanks @Eyelyn9  it worked perfectly for my case.

Super User IV

@Rich_coutinho - 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])``

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User IV

@Rich_coutinho , 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-Y...

Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV

Hi @Rich_coutinho

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Check it Out!

Click here to read more about the April 2021 Updates!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors