cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rich_coutinho
Resolver II
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
Eyelyn9
Community Support
Community Support

Hi @Rich_coutinho ,

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

9.7.1.1.PNG

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

View solution in original post

5 REPLIES 5
Eyelyn9
Community Support
Community Support

Hi @Rich_coutinho ,

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

9.7.1.1.PNG

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

View solution in original post

Thanks @Eyelyn9  it worked perfectly for my case. 

Greg_Deckler
Super User IV
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!




amitchandak
Super User IV
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!

Fowmy
Super User IV
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
PBI User Groups

Welcome to the User Group Public Preview

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

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