CalcDate API

The CalcDate API allows you to retrieve financial report data using date formulas for flexible period selection. This guide provides detailed information about using this endpoint.

Endpoint Details

URL Structure

Online:

https://api.businesscentral.dynamics.com/v2.0/{tenant-id}/{environment}/api/hougaard/financialReport/v2.0/calcDateLines

On-Premises:

https://{server}:{port}/{instance}/api/hougaard/financialReport/v2.0/calcDateLines

For column definitions with more than 12 columns use calcDateLines24, which returns columnValues1 to columnValues24.

HTTP Method

GET

Authentication

OAuth 2.0 (Online)
Basic/Windows Authentication (On-Premises)

Date Formula Parameters

The CalcDate API uses date formulas rather than specific dates:

Parameter	Description	Example
`startDateFormula`	Start of period formula	`-CY` (start of current year)
`endCalcDateFormula`	End of period formula	`CY` (end of current year)

Common Date Formulas

Formula	Meaning
`-CY`	Start of current year
`CY`	End of current year
`-CM`	Start of current month
`CM`	End of current month
`-CQ`	Start of current quarter
`CQ`	End of current quarter
`-1Y`	One year ago
`-1M`	One month ago
`-1D`	Yesterday

Filter Parameters

Optional Filter: reportFilter

Specifies which financial report to use:

$filter=reportFilter eq 'INCOME-STMT'

When omitted, the financial report flagged Default API Report is used. If no report is flagged, the call fails with "No default API report defined".

Date Formula Filters

$filter=startDateFormula eq '-CY' and endCalcDateFormula eq 'CY'

Optional Dimension Filters

$filter=dim1Filter eq 'SALES'
$filter=dim2Filter eq 'REGION-A'
$filter=dim3Filter eq 'PROJECT-1'
$filter=dim4Filter eq 'DEPT-FIN'

Period Type Filter

$filter=periodTypeFilter eq 'Month'

Options (enum Analysis Period Type): Day, Week, Month, Quarter, Year, Accounting Period. The default is Year. Accounting Period must be written as the OData literal Accounting_x0020_Period.

Complete Request Examples

Current Year Data

GET /api/hougaard/financialReport/v2.0/calcDateLines?
  Company=CRONUS%20International&
  $filter=reportFilter eq 'INCOME-STMT' 
  and startDateFormula eq '-CY' 
  and endCalcDateFormula eq 'CY'

Previous Year Comparison

GET /api/hougaard/financialReport/v2.0/calcDateLines?
  Company=CRONUS%20International&
  $filter=reportFilter eq 'INCOME-STMT' 
  and startDateFormula eq '-1Y-CY' 
  and endCalcDateFormula eq '-1Y+CY'

With Dimension Filter

GET /api/hougaard/financialReport/v2.0/calcDateLines?
  Company=CRONUS%20International&
  $filter=reportFilter eq 'INCOME-STMT' 
  and startDateFormula eq '-CY' 
  and endCalcDateFormula eq 'CY'
  and dim1Filter eq 'SALES'

Response Structure

Response Format

{
  "@odata.context": "...",
  "value": [
    {
      "rowNo": "10",
      "description": "Total Revenue",
      "columnValues1": 1500000.00,
      "columnValues2": 1450000.00,
      "columnValues3": 50000.00,
      "amountType": "Net_x0020_Amount",
      "bold": true,
      "italic": false,
      "rowType": "Net_x0020_Change",
      "show": "Yes",
      "totalingType": "Formula",
      "indentation": 0,
      "reportFilter": "INCOME-STMT",
      "periodTypeFilter": "Year",
      "startDateFormula": "-CY",
      "endCalcDateFormula": "CY"
    }
  ]
}

Column Values

columnValues1 through columnValues12 contain calculated values (columnValues1 through columnValues24 on calcDateLines24)
Corresponds to columns in the financial report's column definition
Empty columns always return 0 (never null); header rows with blank Totaling return 0 in every column

Formatting Fields

Use these for rendering:

bold, italic: Text formatting
underline, doubleUnderline: Line styling
indentation: Row hierarchy
show: Visibility rules (Yes, If Any Column Not Zero, When Positive Balance, When Negative Balance; rows with No are never returned, and the conditional values are not evaluated server-side)

Working with Power BI

Power Query M Code

let
    Source = OData.Feed(
        "https://api.businesscentral.dynamics.com/v2.0/{tenant}/{env}/api/hougaard/financialReport/v2.0/calcDateLines?$filter=reportFilter eq 'INCOME-STMT' and startDateFormula eq '-CY' and endCalcDateFormula eq 'CY'",
        null,
        [Implementation="2.0"]
    )
in
    Source

Power BI Service Refresh

For automatic refresh:

Configure gateway (if needed)
Set up credentials in Power BI Service
Configure scheduled refresh

Working with Excel

Power Query Connection

Open Excel
Data → Get Data → From OData Feed
Enter the API URL with filters
Configure authentication
Load data

Dynamic Date Formulas

In Excel, you can't easily change formula parameters. Consider:

Multiple queries for different periods
Custom M functions for parameters
Using DateFilter API instead

Error Handling

Common Errors

HTTP Status	Cause	Solution
401	Authentication failed	Check credentials
403	Forbidden	Verify permissions
404	Endpoint not found	Check URL
400	Bad request	Review filter syntax
500	Server error	Contact support

Invalid Date Formula

An invalid startDateFormula or endCalcDateFormula returns an error response
An omitted formula evaluates to today
Validate formulas before using

Use Cases

Year-to-Date Dashboard

startDateFormula: -CY
endCalcDateFormula: -0D

Shows data from January 1 to today.

Rolling 12 Months

startDateFormula: -1Y
endCalcDateFormula: -0D

Shows last 365 days of data.

Same Period Last Year

startDateFormula: -1Y-CM
endCalcDateFormula: -1Y+CM

Shows the same month from last year.

Full Prior Year

startDateFormula: -1Y-CY
endCalcDateFormula: -1Y+CY

Shows complete previous calendar year.

Best Practices

Performance

Use Specific Reports: Filter by reportFilter
Limit Date Range: Smaller ranges are faster
Add Dimensions: Reduce data with filters

Caching

Implement Caching: Don't call API repeatedly for same data
Refresh Strategically: Set appropriate refresh schedules
Consider Data Volume: Large reports take longer

Date Formulas

Test Formulas: Verify they return expected ranges
Document Usage: Note which formulas you're using
Calendar vs Fiscal: CY, CQ and CM are calendar based; use CP / -CP for accounting periods

Comparison: CalcDate vs DateFilter

Aspect	CalcDate	DateFilter
Period Selection	Date formulas	Specific dates
Flexibility	High	Medium
Best For	Relative periods	Exact dates
Power BI	Works well	Easier parameters

Choose CalcDate when:

You want always-current data
Period should adjust automatically
Using rolling time frames