Calculating Median on Measure

[mukhan169]

Good afternoon,

I am stumped and your help will be greatly appriciated, I am trying to calculate median on the maximum Month from date in table which is October , so for October I want to count July, august and september records and then  calculate the median for those 3 months and then compare the count of October with Median. For example

Month	Count
July	1023
August	455
September	816

The median should be 816 and then I can compare it to the october total. Which is 224. The counts are coming from a measure.

I have following measure but the problem is that it doesnt skip October and when add last "3 Months" It adds August september and October, Instead of July August and September,

Count for 3 Months =

CALCULATE(

       COUNT(ClaimsConvertedData[ID]),DATESINPERIOD(ClaimsConvertedData[PROCDATE], MAX(ClaimsConvertedData[PROCDATE])         ,-3,MONTH)

)

Thank you for your help in advance.

[Anonymous]

Hi @mukhan169,

According to your description, I created some data to show:

1. Create calculation table

sum by month = 
SUMMARIZE('Table','Table'[month],"sum by month",SUM('Table'[salemount]))

1. Create three measures

Mdeiam = CALCULATE(
MEDIAN('sum by month'[sum by month]),
FILTER('sum by month',
'sum by month'[month]>=MAXX(ALL('sum by month'),[month])-3
&&'sum by month'[month]<=MAXX(ALL('sum by month'),[month])-1))

_last_month_sum = 
CALCULATE(
SUM('sum by month'[sum by month]),
FILTER('sum by month',
'sum by month'[month]=MAXX(ALL('sum by month'),[month])))

compare = 
IF(
'sum by month'[_last_month_sum]>'sum by month'[Mdeiam],
TRUE(),
FALSE()
)

1. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mukhan169]

@v-yangliu-msft Thank you for your response. Doing a table is not possible as it has about 10 different slicers. and the total is dependent on what they selected and calculations needed to be done on the fly.

[mukhan169 · Accepted solution]

Thank you all very much I ended up doing the following

Created my 5 measure of last 5 months (As i needed 5 months count to calculate median)

One = calculate (COUNT(ClaimsConvertedData[CustID]), DATESINPERIOD ( ClaimsConvertedData[PROCDATE], EOMONTH ( MAX ( ClaimsConvertedData[PROCDATE] ), -1 ), -1, MONTH ),FILTER(ClaimsConvertedData,ClaimsConvertedData[T&C Identifier]="Y"))

Two = calculate (COUNT(ClaimsConvertedData[CustID]), DATESINPERIOD ( ClaimsConvertedData[PROCDATE], EOMONTH ( MAX ( ClaimsConvertedData[PROCDATE] ), -2 ), -1, MONTH ),FILTER(ClaimsConvertedData,ClaimsConvertedData[T&C Identifier]="Y"))

and so on.

The following is the measure that returns the median.

median = MEDIANX(
Union(
    ROW("Measure", "Measure 1", "Measure Value",[One]),
    ROW("Measure", "Measure 2", "Measure Value",[Two]),
    ROW("Measure", "Measure 3", "Measure Value",[Three]),
    ROW("Measure", "Measure 4", "Measure Value",[Four]),
    ROW("Measure", "Measure 5", "Measure Value",[Five])
),[Measure Value])

[vivran22]

Hey @mukhan169 ,

You may try this:

Last 3 Months =
VAR _CurrentMonth =
    MAX ( SalesTable[Month] )
VAR _CurrentValue =
    MAX ( SalesTable[Count] )
VAR _EndMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1
VAR _Filter =
    DATESINPERIOD ( SalesTable[Month], _EndMonth, -3, MONTH )
VAR _Last3Month =
    IF (
        HASONEVALUE ( SalesTable[Count] ),
        CALCULATE ( MEDIAN ( SalesTable[Count] ), _Filter )
    )
VAR _Compare = _Last3Month - _CurrentValue
RETURN
    _Compare

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[mukhan169]

Hi @vivran22  
Thank you for your help. I am still confuse about

VAR _CurrentValue =
    MAX ( SalesTable[Count] )

As I am unclear how we are calculating the count before we take median.

New try 1 = 
VAR _CurrentMonth =
    MAX ( ClaimsConvertedData[PROCDATE] ) 

VAR _CurrentValue =
    MAX ( ClaimsConvertedData[PROCDATE] )

VAR _EndMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1
VAR _Filter =
    DATESINPERIOD ( ClaimsConvertedData[PROCDATE], _EndMonth, -3, MONTH )

    VAR _Last3Month =
calculate (COUNT(ClaimsConvertedData[ID]),_Filter)
    return _Last3Month

it calculated the count of 2169. for July, August and Spetember should be 1023+455+816=2294 And I am still not sure how to send those values to median.

I am sorry if its silly question but your help is greatly appriciated.

[vivran22]

@mukhan169 

Can you share the sample data/pbix file?

My inputs with comments on the measure below:

New try 1 = 
//Identifies the current month in the filter context
VAR _CurrentMonth =
    MAX ( ClaimsConvertedData[PROCDATE] ) 

//Identifies the current value in the filter context
VAR _CurrentValue =
    MAX ( ClaimsConvertedData[PROCDATE] )

//Identifies the Previous month in the filter context
VAR _EndMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1

//Gives the table with all the dates in last 3 months (excluding current month)
VAR _Filter =
    DATESINPERIOD ( ClaimsConvertedData[PROCDATE], _EndMonth, -3, MONTH )


VAR _Last3Month =
calculate (COUNT(ClaimsConvertedData[ID]),_Filter)
    return _Last3Month

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[mukhan169]

https://drive.google.com/file/d/1oGP4EIHSlV2kDo7SDXMDTCfrQ8lYIYlF/view?usp=sharing

@vivran22 please find the attached samle file. As I explained before For October count I want to calculate count for July, August and september which should be 696,440 and 452 and then calculate the median of them which should be 452. Greatly appriciate your help.

Thank you.

[vivran22]

@mukhan169  I have sent you access request for the file.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[mukhan169]

@vivran22 You should have access now per your request. Sorry about the confusion.

[mukhan169]

@vivran22 I shared a link with you on your

contact@vivran.in email address.

[mukhan169]

https://drive.google.com/file/d/1oGP4EIHSlV2kDo7SDXMDTCfrQ8lYIYlF/view?usp=sharing

@vivran22 I did not receive the request this is the link for the google drive which should be accessiable . If it is not could you please let me know how to give you access?

[amitchandak]

@mukhan169 , Try a measure like example. Prefer to use date table

Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,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 :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[mukhan169]

Thank you , but I am not using the date table and this is the new feature I am adding in the exsisting report.