Dax Formula to to visualize common entries

[coolshib]

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

[LivioLanzo]

Hi @coolshib,

my suggestion is that you build this model:

Then you can do:

Average Invoice Amount = AVERAGE( Invoices[Amount] )

Last Invoice Amount =

AVERAGEX( TOPN( 1, Invoices, Invoices[Invoice Date], DESC ), Invoices[Amount] )

[coolshib]

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

[LivioLanzo]

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

[coolshib]

Hi @LivioLanzo,
No that will not be considered as regular monthly.. I should get minimun a single invoice in every month then only it will be considered as regular monthly vendor.
Regards
Shib

[LivioLanzo]

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?

[coolshib]

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

[LivioLanzo · Accepted solution]

@coolshib

check the file: https://1drv.ms/u/s!AiiWkkwHZChHj04MaHUz8YqTBh1p

[coolshib]

Thank you so much @LivioLanzo.

Works like a charme.

The report delivers what i exactly wanted.

Thanks a ton.

Best Regards

Shib

[coolshib]

Hi @LivioLanzon,
No that will not be considered as regular monthly.. I should get minimun a single invoice in every month then only it will be considered as regular monthly vendor.
Regards
Shib