Date tables - Sage 200

Question

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&nbsp;'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.&nbsp;If I can get somehelp I would really appreciate this as I am unable to do any time intelligencemeasures such as SPLY.&nbsp;KR,&nbsp;Chris&nbsp;&nbsp;

lbendlin · Answer

Your fact table seems to contain a "May 14 1818" date. So that's what the calendar wants to use.&nbsp; Problem is that the Windows calendar only starts on December 30 1899. Hence your gap.
&nbsp;
Note:&nbsp; Read about COALESCE , it can simplify your code quite a bit.
&nbsp;
Dates = CALENDAR(MAX(earliestdate,"1900-01-01"),COALESCE(latestdate,TODAY()))

chris2b · Answer

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:I appreciate the help.KR,Chris2b&nbsp;

lbendlin · Answer

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

chris2b · Answer

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.&nbsp;Thanks&nbsp;

lbendlin · Answer

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

Forum Discussion

Date tables - Sage 200

10 Replies

Related content

Sage 300 date overlap problem.

Power BI Connector for Sage ?

Date Slicer not Responsive to Calculated Date Table

Sage 50

Sage X3