Date in table displaying 01/Jan/1900 instead of blank

[seunao]

Hello,

I am new to Power BI and DAX, and decided to work on creating a ServiceNow dashboard for my service desk in power bi.

I have issues with the below and will be grateful for any assistance.

I am importing my data from SQL into power bi, and I need to create several measures to help build the dashboard.

The first which I am having a challenge with is: "Average days to resolve". the issue I am experiencing is that my table is displaying blank values as 01 Jan 1900. I will like to write a formula that ignores this date and identifies the cell as blank, therefore not use this in the calculation.

Resolved date when blank displays = 01 Jan 1900

The resolved time when blank displays = 00:00:00

to solve this, I created the below metric to measure the elapsed time:

Elapsed time(opened- resolved) = 
    DATEDIFF(min(Incident[Opened Time]),min(Incident[Resolved Time]),Day)

i then add the elapsed time to the opened date to find the average.

Avg. Days to Resolve = 
AVERAGEX (Incident ,
                        DATEADD( Incident[Opened Date], [Elapsed time(opened- resolved)], DAY )
                        )

I need help with excluding the blank cells (01 Jan 1900) from the DAX formula

Number	Opened Date	Resolved Date	number of days in between
inc02	27 Jan 2021	28 Jan 2021	1
inc03	28 Jan 2021	29 Jan 2021	1
inc04	10 Feb 2021	01 Jan 1900	-44235
inc05	11 Feb 2021	01 Jan 1900	-44226

Thank you for helping with this.

[amitchandak]

@seunao , a filter like this can help

filter(Table, not(isblank([Resolved Date]) ) && [Resolved Date]<> date(1900,01,01))

[seunao · Accepted solution]

@amitchandak  - sorry i sent a reply on Saturday but seems my browser timed out and I was unaware. 

I was unable to get the filter to work but that is probably more me not know how to apply it. 

i went about it this way 

Avg Days to Resolve = AVERAGEX(FILTER(
    'Incident',
    DATEDIFF('Incident'[Opened Date],'Incident'[Resolved Date],Day)>0),
        DATEDIFF(Incident[Opened Date],Incident[Resolved Date],DAY))

Thanks for your help