Forum Discussion
Odatalink Xero Accounting - Daily view
Hi all,
I am trying to create a daily view for Profit and loss but the endpoint I am using only breaks down to Month, Quarter or Year.
My solution is to input a start and end date paramenter of the same day eg (01/01/2024 & 01/01/2024) and then re run the call for the next day and next day and so on and append into a table.
A bit of help ned though.
Firstly is there a better solution to the one I am considering.
Two, does anyone have an example I can use? or point me to a tutorial that will help.
Thanks
Xero Accounting Endpoint: ProfitAndLossAdvanced - OdataLink
4 Replies
- dufoq3
Community Champion
Hi, provide sample data and expected result please.
https://drive.google.com/file/d/1mS08cgC65tybihgeG5e48LNZaBJXoaAu/view?usp=sharing
PBIX file at the above link.Screenshot here - data currently grouped monthly (end of month) trying to get down to the daily level
I know this is an old post, but there are 2 approaches you can take with OdataLink.
First up, you could perserve using the ProfitAndLoss endpoint but it would probably come at a cost in terms of performance as you would be running 365 calls for each year. If you wantr to continue down this path, you would need a Dates table and Invoke the ProfitAndLoss for each of those dates using the "Add Column \ Invoke Custom Function".
That being said, the approach i would recommend would be to use JournalsAccrual, JournalsCash or JournalsAccrualTable endpoint. Those endpoints provide all debits/credit amounts for a given date range. The first two are in the raw format that Xero provides while the later is a flattened Table format more suitable for Tableau/Azure Data Factory, etc.https://help.odatalink.com/index.php?title=Xero_Accounting_Endpoint:_JournalsAccrual
Hope this points you in the right direction.
Regards
Nic
The way to do this is as follows:
1) Reference both endpoints
2) Remove the invoke step from the ProfitAndLoss endpoint
