Forum Discussion
Helper IIICalculating Median on Measure
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])
Community ChampionHey 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
mukhan169Hi vivran22
Thank you for your help. I am still confuse aboutVAR _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 _Last3Monthit 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
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 _Last3MonthCheers!
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.