Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Emmy88_
Frequent Visitor

Formatting error with datetable - userelationship

Hi all,

 

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.

 

Emmy88__1-1674744500928.png

 

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. 

Emmy88__0-1674744392271.png

 

However both the datetable and the completion date in the facttable are dates:

 

Emmy88__2-1674744740181.png

 

 

Does anyone have an idea on why it shows the error?

 

Thank you,

 

Emmy

 

 

 

5 REPLIES 5
olgad
Super User
Super User

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))


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
Emmy88_
Frequent Visitor

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:

Emmy88__0-1674749346585.png

 

It should show 1 item in the last 30 days completed:

 

Emmy88__1-1674749670049.png

 

 

 

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


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
olgad
Super User
Super User

Hi, 
is Date in datetable also a date format?? you show here only the completion date. 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
Emmy88_
Frequent Visitor

It indeed is:

 

Emmy88__0-1674746708614.png

 

Or am i missing something?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.