optimisation of mobile median in dax

[Josee]

Hello,

I would like to optimise the calculation below. I guess if function or switch function would be helpfull. 
Do you have any recommandation ?

MobileMedian3period=

= ( calculate( MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=1))

+ calculate(MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=2))

+ calculate(MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=3))

)/3

[Josee]

Hello, 

This code is more efficient and leaner:
1) creation of a measure 

med_waiting = MEDIAN(waitingtable[nMb_days_waiting_total])

2) creation of this second measure

MobileMedian3period =

VAR MedM = CALCULATE(

AVERAGEX(

VALUES(table_year_period[no_periode(row)]),

[Med_waiting]

),

table_an_periode[no_periode(row)] <= 3

)

RETURN MedM

[Josee]

My waiting list don't have the same number of observations by period. To calculate a mobile median(mean of the 3 median), I need to calculate independently the median for these last 3 periods.
( I also perform the same for 13 period/a year). 
It would be more effective if the calculation could go through all observation once or, until it is used like a else if .

[danextian]

Hi @Josee ,

If that wasn't the solution you were looking for, please provide a sample data and your expected result. Here's a guide on how the community can better answer your queries: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071... 

[danextian]

Hi @Josee ,

You can actually add an or statement in filter so you can write your measuer this way:

MobileMedian3period =
DIVIDE (
    CALCULATE (
        MEDIAN ( waitingtable[nNb_days_waiting_total] ),
        FILTER (
            table_year_period,
            table_year_period[no_periode(row)] >= 1
                || table_year_period[no_periode(row)] <= 3
        )
    ),
    3
)