Power BI with your accounting data

BIDates - Suntico BI Dates Table for Sage 50 Accounts

Posted by Hugh Johnson on 28-Jul-2018 15:22:00
Hugh Johnson
Find me on:

Sage 50 Accounts OData feed for Power BI

This article describes the BIDates table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  

This table is essential if you want to analyse your various Sage 50 Accounts data tables by date or period.

BIDates table

 

 
Column Type Description
DateID Text Internal Date ID to link to the BIProductSales, BIPurchaseOrders, BISalesOrders, and BITransSplits tables
Date Date/Time Date
Month Text 3-alpha text description of the month of the date - e.g. "Jan", "Feb", "Mar" etc. 
Day Whole Number Day of the month. For example this would have a value of "30" for 30th January. 
Week Whole Number Week number of the financial year of the company.  Weeks run from Sunday to Saturday.
Yesterday True/False True if the Date is yesterday
Today True/False True if the Date is today
Last28Days True/False True if the Date is in the last 28 days up to and including yesterday
Last91Days True/False True if the Date is in the last 91 days up to and including yesterday
Last52Weeks True/False True if the Date is in the last 364 days up to and including yesterday
ThisWeek True/False True if the Date is in this Week
NextWeek True/False True if the Date is in next Week
LastWeek True/False True if the Date is in last Week
ThisMonth True/False True if the Date is in this Month
NextMonth True/False True if the Date is in next Month
LastMonth True/False True if the Date is in last Month
ThisYear True/False True if the Date is in this financial year.  So for example if this financial year starts on 1st April 2018, then it will be True for all Dates from 1st April 2018 to 31st March 2019 inclusive.  This value is not dependent on whether or not your Sage financial year is closed off
LastYear True/False True if the Date is in your last financial year.  So for example if your last financial year started on 1st April 2017, then it will be True for all Dates from 1st April 2017 to 31st March 2018 inclusive.  This value is not dependent on whether or not your Sage financial year is closed off
NextYear True/False True if the Date is in your next financial year.  So for example if your next financial year starts on 1st April 2019, then it will be True for all Dates from 1st April 2019 to 31st March 2020 inclusive.  This value is not dependent on whether or not your last Sage financial year is closed off
ThisYearOrLast True/False True if ThisYear OR LastYear are True
ThisYTD True/False True if the Date is in this financial year up to and including yesterday
LastYTD True/False True if the Date is in last financial year up to and including yesterday (one year ago)
YrStart True/False True if the Date is the first day of one of your financial years
YrEnd True/False True if the Date is the last day of one of your financial years
Quarter Whole Number Quarter number of your financial year that the Date is in.  For example if your financial year starts on 1st April, then 22nd May will have the value "Quarter = 1"
Period Whole Number The accounting period (month) that the date falls into.  For example if your financial year starts on 1st April, then 22nd May will have the value "Period = 2"
DaysInPeriod Whole Number The number of Days in the Period (month)
Year Text "This Year" if the Date falls into your current financial year, "Last Year" if the Date falls into your last financial year, "Other for all other dates
AgedWeeks Text The number of whole weeks old the date is relative to today.  For example 7 to 13 Days ago from today will have a value of "1". 14 to 20 days ago will have a value of "2" etc..  All Dates equal to or higher than six days ago from today will have a value of "0"
AgedPeriod Text "< 30" if the Date is higher than 30 days ago, "30 to 59" if the Date is between 30 and 59 days old inclusive, "60 to 89" if the Date is between 60 and 89 days old inclusive and "90 +" if the Date is older than 89 days
PeriodID Text Internal reference to link to the BIPeriods table
YrSortOrder Whole Number Internal reference to sort the [Year] column correctly
SageYear True/False Date is in the currently open year in Sage.  Normally this will be the same as "ThisYear" but if the new financial year has started but the previous year not yet closed off in Sage then "SageYear" will equal "LastYear"

BIDates relationships

The BIDates table in the OData feed is related to the BIProductSales, BIPurchaseOrders, BISalesOrders, and BITransSplits tables via the [DateID]. 

 Dates table relationships to Product Sales, Sales Orders, Purchase Orders, Periods and TransSplits

To try our this OData feed for your Sage 50 Accounts data, why not create your own Suntico BI trial account?

Create Suntico BI Trial Account 

 

Topics: ODBC, Power BI Desktop, OData

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts