Average and Median DAX calculating incorectly

[POSPOS]

Hi All,

I have a requirement to calculate the average and median between two dates. 
Issue: Median is taking blank records when calculating.

Sample date:

Protocol Number	Start	End
19-0402	06/24/2019	08/12/2019
19-0502	08/07/2019	10-23-2019
22-0502	02/06/2019	03/08/2023
22-0503	11/02/2022

I have created a column to calculate the datediff

DATEDIFF(start,end,day)

Protocol Number	Start	End	Datediff
19-0402	06/24/2019	08/12/2019	49
19-0502	08/07/2019	10-23-2019	57
22-0502	02/06/2023	03/08/2023	30
22-0503	11/02/2022

Then, I created another measure to display average and median in "Avg/Median" format

Avg/Median = 
VAR a= AVERAGEX(
    VALUES('Avg'[Protocol Number]),
    CALCULATE(AVERAGE('Avg'[Datedifference])))
VAR b=MEDIANX(
    VALUES('Avg'[Protocol Number]),
    CALCULATE(MEDIAN('Avg'[Datedifference])))
RETURN 
ROUND(a,0)&" / "& ROUND(b,0)

As there is no end date for the the last record in the table, the datediff is blank.
Issue:  median is getting calculate based on 4 records instead of three records. 
Average  calcualtion is showing correctly for three records.
49+57+30=136 : Avg=136/3 = 45
Whereas the median is being calculated for 4 records. 0,30,49,57 = (30+49)/2=40

I would like the median also to be calculated for three records excluding blank record i.e., 30,49,57.
Expected result is 49 but DAX is giving 40.

Can someone please suggest on how to fix this issue. Also attaching here sample pbix file in the link. 
https://drive.google.com/file/d/1UkRN8IMLpsXRFSzR58s_tUbg1Bd-bsDY/view
Thank you.

[rameezPatel · Accepted solution]

Hi @POSPOS ,

try the following

Avg/Median =

VAR a= AVERAGEX(

    VALUES('sample'[Protocol Number]),

    CALCULATE(AVERAGE('sample'[Datediff])))

VAR b=

MEDIANX(

    SUMMARIZE(FILTER('sample',NOT(ISBLANK('sample'[Datediff]))),'sample'[Protocol Number],"mid",median('sample'[Datediff])),[mid]

  )  

RETURN

ROUND(a,0)&" / "&ROUND(b,0)

main different with average and Median is that average ignores blanks and Median doesnt.
so you need to filter out the blanks in your dax, 
so this this work for you .
If this approach suits your needs, kindly show your support by voting. 😊

[POSPOS]

@rameezPatel 
Could you also please suggest on how we can write similar code for median to ignore blank records when the dates are from two different tables and the datediff is in the first table. Now I want to write this code with Table 2 as primary table.

Sample date is below - 
Table1:

Protocol No	Date1	Datediff
19	06/24/2019	49
20	08/07/2019	57
21	02/06/2023	30

Table2:

Protocol No	Date 2
19	08/12/2019
20	10/23/2019
21	03/08/2023
22	11/02/2022

[POSPOS]

@rameezPatel  Thank you for helping fix the issue. I am now getting expected results.

[POSPOS]

@ryan_mayu 
Either the start date or the end date could be blank . Logic for Average is working but median values are incorrect.
I did attach an updated pbix file a while ago with better data. Could you please help with that to fix the issue.
I have use the below code but does not give correct result for median.

Avg/Median = 
VAR a= AVERAGEX(
    VALUES('sample'[Protocol Number]),
    CALCULATE(AVERAGE('sample'[Datediff])))
VAR b=MEDIAN('sample'[Datediff])
RETURN 
ROUND(a,0)&" / "& ROUND(b,0)

[ryan_mayu]

@POSPOS 

maybe you can try this

Datediff = if(ISBLANK('Table'[End]),0,DATEDIFF('Table'[Start],'Table'[End],DAY))

avg = AVERAGEX(FILTER('Table','Table'[Datediff]<>0),'Table'[Datediff])

median = MEDIAN('Table'[Datediff])

pls see the attachment below