Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I am looking for a formula/solution to prepare a report of my all vendors whos invoices i receive every month.
For example, i have hundreds of vendors with whome i have been doing business. Some of them i do business once or twice in a year, but there are some vendors with whome i do business every month.
I have a very big data as per below format.
Invoice Date | Vendor Name | Amount |
01/05/2018 | ABC Company | 15000 |
25/07/2018 | XYZ Company | 25000 |
19/07/2018 | ABC Company | 35000 |
I am looking for a solution which gives me the following report
Vendor Name | Average Invoice Amount | Last Invoice Amount |
ABC Company | 32,500.00 | 35,000.00 |
Hope i have managed to explain my query.
Thanks in Advance.
Best Regards
Shib
Solved! Go to Solution.
check the file: https://1drv.ms/u/s!AiiWkkwHZChHj04MaHUz8YqTBh1p
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @coolshib,
my suggestion is that you build this model:
Then you can do:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
hi @LivioLanzo,
Thank you so much for your prompt response.
Just wanted to check whether this model would also filter the vendor name whose invoices i receive every month not the irregular ones.
Also i want month wise average invoice value to include in this report.
Thanks alot.
Best Regards
Shib
Hi @coolshib,
yes it can be done but there needs to be a logic behind the invoices that are considered to be received monthly. What if the vendor sends the invoice monthly and then stop for 1 month and then start again? Are they still considered monthly? If yes, how many months of interruption are they allowed to have? There needs to be some categorization done
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @coolshib
are you able to post a larger data set? Are you trying to filter out vendors who do not send an invoice each month?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo,
Yes i want to filter out vendors who do not send an invoice each month.
Please find below the sample data set for your reference. The period of the Data set is Jan 2018 - Nov 2018.
Month wise Vendors Report.xlsx
I am trying to prepare the report of the vendors who send me invoice each month as per below format.
Vendor Name | Monthly Average of Invoice Amount | Average of Invoice Amount | Last Invoice Amount |
Best Regards
Shib
check the file: https://1drv.ms/u/s!AiiWkkwHZChHj04MaHUz8YqTBh1p
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you so much @LivioLanzo.
Works like a charme.
The report delivers what i exactly wanted.
Thanks a ton.
Best Regards
Shib
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |