BIDates - Power BI Dates Table for Sage 50 Accounts


by Hugh Johnson, Posted on 7/28/2018 1:57:59 PM

 ODBC, Power BI Desktop, OData

BIDates - Power BI Dates Table for Sage 50 Accounts

Sage 50 Accounts OData feed for Power BI

This article describes the BIDates table in the Suntico Sage 50 Accounts OData feed that you have set to as one of your Power BI data sources.  

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

BIDates table

 
ColumnTypeDescription
DateIDTextInternal Date ID to link to the BIProductSales, BIPurchaseOrders, BISalesOrders, and BITransSplits tables
DateDate/TimeDate
MonthText3-alpha text description of the month of the date - e.g. "Jan", "Feb", "Mar" etc. 
DayWhole NumberDay of the month. For example this would have a value of "30" for 30th January. 
WeekWhole NumberWeek number of the financial year of the company.  Weeks run from Sunday to Saturday.
YesterdayTrue/FalseTrue if the Date is yesterday
TodayTrue/FalseTrue if the Date is today
Last28DaysTrue/FalseTrue if the Date is in the last 28 days up to and including yesterday
Last91DaysTrue/FalseTrue if the Date is in the last 91 days up to and including yesterday
Last52WeeksTrue/FalseTrue if the Date is in the last 364 days up to and including yesterday
ThisWeekTrue/FalseTrue if the Date is in this Week
NextWeekTrue/FalseTrue if the Date is in next Week
LastWeekTrue/FalseTrue if the Date is in last Week
ThisMonthTrue/FalseTrue if the Date is in this Month
NextMonthTrue/FalseTrue if the Date is in next Month
LastMonthTrue/FalseTrue if the Date is in last Month
ThisYearTrue/FalseTrue 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
LastYearTrue/FalseTrue 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
NextYearTrue/FalseTrue 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
ThisYearOrLastTrue/FalseTrue if ThisYear OR LastYear are True
ThisYTDTrue/FalseTrue if the Date is in this financial year up to and including yesterday
LastYTDTrue/FalseTrue if the Date is in last financial year up to and including yesterday (one year ago)
YrStartTrue/FalseTrue if the Date is the first day of one of your financial years
YrEndTrue/FalseTrue if the Date is the last day of one of your financial years
QuarterWhole NumberQuarter 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"
PeriodWhole NumberThe 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"
DaysInPeriodWhole NumberThe number of Days in the Period (month)
YearText"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
AgedWeeksTextThe 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"
AgedPeriodText"< 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
PeriodIDTextInternal reference to link to the BIPeriods table
YrSortOrderWhole NumberInternal reference to sort the [Year] column correctly
SageYearTrue/FalseDate 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 BIProductSalesBIPurchaseOrdersBISalesOrders, and BITransSplits tables via the [DateID]. 

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

 

 


Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,
Dublin,
Ireland

+353 1 567 4069

[email protected]