BUG - [your bug title here]

[ayushonplan]

Are you a current Fivetran customer? yes

Describe the bug In the report generated by Xero we have some field values positive for PL but in the report generated by fivetran dbt package of column net_amount we have those value as negative for some accounts although we have positive values in the source table but the transformation written in the dbt doesn't fetch those values .

Steps to reproduce 1.Go to the Fivetran dashboard and create a connector using Xero as the source and BigQuery as the destination.

2. add transformation for connector using Fivetran Xero DBT package.
3. after running sync you will get reports generated through the DBT package in BigQuery. 4.Now Just compare reports value with original Xero reports.

Expected behavior we should get the values for net_income positive in the final report Project variables configuration

name: 'client_name'
profile: 'my-bigquery-db'
version: '0.3.0'
config-version: 2
require-dbt-version: [">=0.20.0"]

vars:
  xero_schema: xero_five_tran
  xero_database: 
  xero_five_tran:
    account: "{{ ref('stg_xero__account') }}" 
    contact: "{{ ref('stg_xero__contact') }}" 
    invoice_line_item: "{{ ref('stg_xero__invoice_line_item') }}" 
    invoice: "{{ ref('stg_xero__invoice') }}" 
    journal_line: "{{ ref('stg_xero__journal_line') }}" 
    journal: "{{ ref('stg_xero__journal') }}" 
    organization: "{{ ref('stg_xero__organization') }}" 
    credit_note: "{{ ref('stg_xero__credit_note') }}" 
    bank_transaction: "{{ ref('stg_xero__bank_transaction') }}" 

models:
 :
    +materialized: table
    +schema: xero

Package Version package: fivetran/xero_source version: [">=0.2.0","<0.3.0"]

packages:
  - package: fivetran/xero_source
    version: [">=0.3.0","<0.4.0"]

Warehouse

- [x] BigQuery - [ ] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency This is really important because, the client needs the report, but as I told the report we are generating through the Fivetran DBT package has the wrong net_amount which is blocking us. This will really affect our relationship with the customer. If we don't deliver it on time it may result in fine also.

Are you interested in contributing to this package? no

• [ ] Yes, I can do this and open a PR for your review.
• [ ] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [x] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[JoeMark17]

Hi @ayushonplan

Thanks for opening this issue. It seems the issue you are describing is that the net_value amounts within the final dbt model are not matching the Xero UI generated reports. I actually previously helped an individual with a similar question in this Issue.

Would you be able refer to the resolution we achieved in the previous issue and let me know if that resolves the discrepancy you are seeing as well.

Thanks!

[ayushonplan]

Hi Joe, The issue is not with the wrong values, the actual issue is with the signs . We are getting right values but signs are not matching suppose we have a value 3000 in the fivetran report but we have -3000 in the source that is stg_xero__journal_line

On Thu, Nov 25, 2021 at 9:09 PM Joe Markiewicz @.***> wrote:

Hi @ayushonplan https://github.com/ayushonplan

Thanks for opening this issue. It seems the issue you are describing is that the net_value amounts within the final dbt model are not matching the Xero UI generated reports. I actually previously helped an individual with a similar question in this Issue https://github.com/fivetran/dbt_xero/issues/14.

Would you be able refer to the resolution we achieved in the previous issue and let me know if that resolves the discrepancy you are seeing as well.

Thanks!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_xero/issues/20#issuecomment-979311111, or unsubscribe https://github.com/notifications/unsubscribe-auth/AWVDDSBWFHTTRE2JM57ZQELUNZKEXANCNFSM5IYZUUJQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[fivetran-joemarkiewicz]

Thanks for the clarification @ayushonplan

I would imagine the inverse values are a result of the journal entry is a credit and the Xero is recognizing the increase as a negative value. We then adjust for this in the final model by multiplying by -1 to ensure the final PL reports do not display negative values when they are in fact increases.

https://github.com/fivetran/dbt_xero/blob/76336754f6e538956afd77543ff24a2ac19a2a4e/models/xero__profit_and_loss_report.sql#L22

Would you be able to confirm the account that you are noticing this for. My suspicion is that it is an account that is increased with a credit and that is why the journal entries are showing negative values, when in fact they should be represented as positive values in the P&L (i.e a revenue amount will usually be adjusted to be positive and an expense to be negative).

[fivetran-joemarkiewicz]

HI @ayushonplan 👋

I wanted to check back in if my above explanation helped answer your question?

[fivetran-joemarkiewicz]

Hi @ayushonplan 👋

I will be closing this issue as I believe the above message addresses your initial question. Please feel free to re-open this issue if you would like to discuss further.

Thanks!