I have a fact table with different date types e.g. 'completed date', 'due date'.
I have mapped those to a datetable and want to show a total # actions completed for the last 30days. For that i used underneath formula, as the completion date is an inactive relation with the datetable.
I receive underneath error. Sorry for the dutch, but it seems there is a formatting error: it does not support comparing values with text and date format.
However both the datetable and the completion date in the facttable are dates:
Does anyone have an idea on why it shows the error?
Calendarauto() very often leads to some issues, you would often use it with a defined column
CALENDARAUTO([fiscal_year_end_month]) if you have any other date column in report which is not date, that might be playing tricks, since Calendarauto takes the min and max date. You dont even have the calendar icon next to date column.
Either use it with the column or may be try to use simply Calendar function.
calendar = CALENDAR(DATE(2019,1,1), DATE(YEAR(TODAY()), 12,31))
Thank you for the tip. i did change the calendarauto function to the calendar function, now the error message does not show. However no data is showing at all:
It should show 1 item in the last 30 days completed:
So now the format seems fine but the function does not work. Is the function in essence correctly set? (first time i use userelationship.
Now that you changed your date table calculation, check if the inactive relationship between it and completion date is still there
AND usually Date is Date/Time when you create with Calendar, change to Date. if not yet
is Date in datetable also a date format?? you show here only the completion date.
It indeed is:
Or am i missing something?
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.