Forum Discussion

mukhan169's avatar
mukhan169
Icon for Helper III rankHelper III
5 years ago
Solved

Calculating Median on Measure

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...
Need Help
  • mukhan169's avatar
    mukhan169
    5 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    mukhan169
    Icon for Helper III rankHelper III
    5 years ago

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