Vendor Aging Report

[deeksha]

Hi All,

I am a relatively new user in Power BI and am needing assistance with the following requirement.

I need to make a vendor aging report in power bi and i have to get data through data entities using OData but I don't know Through which data entity I can get the values for vendor aging report like vendor group, name, aging as of, balance as of, voucher/invoice and balances as for 3 months and respectively.

Can someone urgently assist me with the requirement as to which data entity should be used to get all these values?

[TomMartens]

Hey @deeksha 

ODATA is "just" an interface to a data source, you have to consult the owner of the data source, where you can find all the fields you need. Maybe it will become necessary to retrieve more than one table / define more than ODATA connection and finally join the tables accordingly inside Power BI.

Regards,

Tom 

[deeksha]

I have to create a vendor aging report in power bi just like the one in d365, and I am trying to get the data using OData but i don't know from which data entity or tables I can get the values like account number, vendor name, group and their balances as of different months, this I want to know, from which table I will get these fields and values.

[armely]

There are a number of datasets you can get from different marketplaces, here is a sample source https://docs.microsoft.com/en-us/power-bi/sample-datasets.

[deeksha]

Sorry, maybe you are not getting my question, I need the name of the data entity or table where I can the vendor aging report fields and values.

[armely]

I think am tracking now your data source is D365 right? If that's correct your tables will be customer Details table
https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/customer-aging-report-custagingreport

[deeksha]

for customer aging report the customer details table will be used and what about the vendor aging report? which table will be used for it for the balances as of field?

[armely · Accepted solution]

This for vendor https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/vendor-aging-report-vendagingreport

[Anonymous]

1 – Create Two Date Tables
Create two date tables in DAX—one for our Document Dates, and the other for our Posting Dates. They can be named anything and can have as few or as many columns to satisfy reporting requirements. For this example, we will create a basic calendar using Power BI’s CALENDAR function. The exact usage of these tables will be detailed in subsequent steps.

2 – Join to Our Date Tables
Create a single directional relationship from the [Posting Date] on the Fact Vendor Ledger Entry table to the ‘Posting Dates’[Date] field as well as from the [Document Date] on the Fact Vendor Ledger Entry table to the ‘Document Dates’[Date] field.

3 – Create “As-Of Date” Slicer
Use ‘Posting Dates’[Date] to create an “As-Of Date” slicer that will filter out the dataset based on the date selected. We will select the option of ‘Before’, so the user is only allowed to select one date. The date selected will be referenced as MAX(‘Posting Dates’[Date]) in the created measure.

4 – Create Measures
Create measures on ‘Fact_Vendor Ledger Entry’ for each of our aging buckets. The code below is for the 31-60 Day Aging Bucket. All buckets will need their own specific measure.

[deeksha]

Extremely sorry for replying so late, but from where I will get the values for the vendor aging report? I need the name of the data entity from which I can get data in my report, which data entity has these related values?

[armely]

Am a little confused on your request, are you looking for sample data or you already have a data source?

[deeksha]

No, I don't have a data source, I need the names of the tables from where I can get the data for the vendor aging report.

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information. And expected outcome

Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...