Forum Discussion
date 01-01-1
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)
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.
3 Replies
You could update the value in Power Query to NULL, Blank or the minimum date you want in your table.
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.
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 tablein 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 !!
