cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Draszor
Helper II
Helper II

Invoices processing KPI

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 IDVIM process statusGross inv amountDocument datedue dateenter onposted onpaid on  
54364

 

Posted

2002020-11-092020-12-092020-11-102020-12-102020-12-22  
54365Cancelled1002020-08-102020-10-102020-08-12    
5425961posted4002020-12-012021-01-012020-12-032020-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 nameKPI reference to VIMMeasure
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" - 30number 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

1 ACCEPTED SOLUTION
AllisonKennedy
Super User III
Super User III

@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.



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User III
Super User III

@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.



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

AllisonKennedy
Super User III
Super User III

@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

 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors