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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.