Invoices processing KPI

[Draszor]

Hi, 

We have a vendor invocices management system containing all incomming invoices for the company. each row of this system represents one document, each column represents one of its characteristics with the most important ones as follow:

Document ID	VIM process status	Gross inv amount	Document date	due date	enter on	posted on	paid on
54364	Posted	200	2020-11-09	2020-12-09	2020-11-10	2020-12-10	2020-12-22
54365	Cancelled	100	2020-08-10	2020-10-10	2020-08-12
5425961	posted	400	2020-12-01	2021-01-01	2020-12-03	2020-12-15

VIM Process Status - Posted/osolete/cancelled/blocked/indexed etc - generally for further KPI's I am interested in only "Posted"status

Document date - date on the invoice

Due date - date when the invoice should be paid

Enter on - date when the invoice was entered into VIM

Posted on - date when the accounting posting was done (between entering into the system and posting, the invoice is processed and checked for Purchase Order existence, vendor validity etc, many other checks)

Paid on - the date invoice is paid

I would like to create a new date table containing all the dates from 1-1-2020 until today and for every date (Every D) I would like to show graphically (or in the table the following KPI:

KPI name	KPI reference to VIM	Measure
not processed invoices for each date (D)	invoices where: "Enter on" <= "D" < "Posted on"	number of such invoices and their total amount
not processed invoices >5 days (or 10, or 15 days)	invoices where: "Enter on" <= "D" < "Posted on" - 5 (or minus 10 or minus 15 days)	number of such invoices and their total amount
overdue payments >30 days	"Due Date" <= "D" <"Payment Date" - 30	number of such invoices and their total amount

such KPI would be very helpful in following up the processing time (too long now) and payments. 

I was searching for any similarities in different posts but did not find anything that would solve the above.

thank you for any help

BR

Draszor

[AllisonKennedy · Accepted solution]

@Draszor  Using the CALENDAR function for date table is a bit more work (you have to add all the other columns for month, week, etc) and less efficient for the report, but it will work. 

So you have two tables now: Date and Fact. The Fact table has [EnterDate], [PostDate] 

So create a new measure: 

Not Processed Invoices = 

VAR _date = MAX(Date[Date])

RETURN

CALCULATE(COUNTROWS(FILTER(Fact, Fact[EnterDate] <= _date && Fact[EnterDate] < Fact[PostDate]), ALL(Date))

Then you will need to put this measure in visual with Date[Date] in the visual or as a slicer on the page.

[AllisonKennedy]

@Draszor  Here is a data table you can use: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

And you can either use the ALL(Date) function or unrelated date table and then add your filter conditions similar to this approximate DAX Lookup: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html

[Draszor]

hi, 

for a new date table I was going to use:

Date = CALENDAR(DATE(2020,1,1),TODAY())

then, what I struggle with:

• taking 2020-09-20, I need to count all such documents that were processed at this date (so, fulfil the condition "Enter on" <= 2020-09-20 < "Posted on", for "Not processed invoices" KPI
• such calculation needs to be done for each date from the newly created DATE table, starting from 2020-01-01 until today
• my DAX is too poor yet to see the formula that could make such trick. I see what? and why? but not how?

any hint? maybe it should not be done in DAX but in the query with C language? as said, not enough experience to have clarity, but I am trying to cath up as you see 😉

thx