date 01-01-1

[powerbiexpert22]

i am using survey table in my data model which has last_response_date column

last_response_date has value '01-01-1900' for around 30 surveys

i created calendar table using below expression by ignoring '01-01-1900' to avoid creating huge calendar table

if i do this then it is ignoring 30 records in my report

is there a way to create date table without ignoring 30 records ?

Date = 
    VAR MinDate = CALCULATE(MIN(survey[last_response_date]), survey[last_response_date] <> DATE(1900, 1, 1))
    VAR MaxDate = CALCULATE(MAX(survey[last_response_date]), survey[last_response_date] <> DATE(1900, 1, 1))
    RETURN
        CALENDAR(MinDate, MaxDate)

[anmolmalviya05]

Hi @powerbiexpert22, Hope you are doing good!

If you don't want to create entire date table then you can follow below workaround:

Keep your date table as it is
Create a new table manually (using enter data) having only one row for 01-01-1900
Append the table to your date table

in this way you will have all records in your table or visuals

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous · Accepted solution]

Hi, @powerbiexpert22 

Perhaps you need to sacrifice your computer's performance and create an entire date table.

Date = 
    VAR MinDate = CALCULATE(MIN(survey[last_response_date]))
    VAR MaxDate = CALCULATE(MAX(survey[last_response_date]))
    RETURN
        CALENDAR(MinDate, MaxDate)

 And then create a calculated column or measure to filter out other values other than 1-1-1900.

FilteredLastResponseDate = 
    IF(survey[last_response_date] = DATE(1900, 1, 1), BLANK(), survey[last_response_date])

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

You could update the value in Power Query to NULL, Blank or the minimum date you want in your table.