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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Chris2B
Helper I
Helper I

Date tables - Sage 200

Hi I need somehelp with my date table.

I have a live dataset that has been integrated from Sage 200, however I am having a major issue with my date table. It is pulling all the way from 1818 instead of 2018. The dat range that I want it to pull from is 'Invoice Credit Line View'[sop_invoice_credit_document_date]. Its not allowing me to use that as the date table as it says that there are not consecutive dates.

 
If I can get somehelp I would really appreciate this as I am unable to do any time intelligencemeasures such as SPLY.
 
KR,
 
Chris

 

Chris2B_0-1709590122659.png

 

10 REPLIES 10
Chris2B
Helper I
Helper I

I have tried using Chat GPT to amend the Max values and I have gotten a new error message:

Chris2B_0-1709747680743.png

 

 

that would be in line 18. 

 

Your overall formula looks like it can be simplified too.  Can you formulate the business rules for earliestdate and latestdate?

I've corrected the Max in line 18 and it coes up with this, how I simplyfy it?

 

Chris2B_0-1709750743005.png

 

Why did you change line 26?    needs MAX, not MIN.

 

At this point it might be easier if you would provide a sample of your data model and data.

Sorry it must have been a typo.

I have corrected it and it is still giving me the same message.

thi is the model as it was pulled directly from Sage 200, it is very messy and that is why I am having troubles with it.

I have managed to do basic sales and costs calculations but I am unable to make any time calculated measures.

 

THis is what the model view looks like, what sample data would you like?

:

Chris2B_0-1709761048671.png

 

Chris2B
Helper I
Helper I

Sorry I am not understanding? I have tried adding a closing parenthesis but I still am getting errors. I sipossible to help me rewrite it.

 

Thanks

 

 CALENDAR(MAX(earliestdate,"1900-01-01"),COALESCE(latestdate,TODAY()))

lbendlin
Super User
Super User

Your fact table seems to contain a "May 14 1818" date. So that's what the calendar wants to use.  Problem is that the Windows calendar only starts on December 30 1899. Hence your gap.

 

Note:  Read about COALESCE , it can simplify your code quite a bit.

 

Dates = CALENDAR(MAX(earliestdate,"1900-01-01"),COALESCE(latestdate,TODAY()))

Hi Ibendin,

Thanks for this, it is a great help. I have tried implementing the COALSCE to remove the gap but I am still getting an error code:

Chris2B_1-1709636248003.png



I appreciate the help.

KR,

Chris2b

 

you are missing a closing parenthesis after the second argument of MAX()

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Kudoed Authors