Forum Discussion
Backlog evolution at month end
Hello Comunity!
I'm trying to figure out how to calculate the backlog of documents created (invoices) that still were not submitted to customer at month end and see it in a chart to monitor the evolution month to month BUT also be able to select any month and see in a table the list of invoices that are part of that backlog.
My current solution is to do it in excel with formulas generateing the "backlog database" for each month, and appending each month on a long database with the corresponding backlog month column.
The logic for each month end (e.g. 31/Jan/2010) is:
If the document was already submitted (Status: Y), it is backlog at month end if it was invoiced before or in the backlog date and submitted after that date.
If the document was still not submitted (Status: N), it is backlog at month end if it was invoiced before or in the backlog date.
Data:
Invoice Number
Invoice Volume
Invoice Date
Submission Date (never is black, if not submitted it has the estimated submission date, otherwise, the actual one)
Status (Y= Submitted, N=Not submitted)
Example in excel attached. My desired solution would be to avoid appending and get it with calculations but I'm not being able to reach a solution. Second option is maybe replicate the process of append in a Power Query but I do not know how to do it.
Thanks in advance!
You can create a formula like this after having a date table
backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None)) backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))+0with ), you have control display of month
You can build yes and no on top of it
backlog status = if([backlog]=1,"Y","N")Link of solution:https://www.dropbox.com/s/d7vvbk4zekhsx5t/backlog.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
8 Replies
I did not find the excel link.
Thanks for letting me know. I have edited the post to include it.
You can create a formula like this after having a date table
backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None)) backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))+0with ), you have control display of month
You can build yes and no on top of it
backlog status = if([backlog]=1,"Y","N")Link of solution:https://www.dropbox.com/s/d7vvbk4zekhsx5t/backlog.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Hi pratafran ,
You could create a data dimension based on your "Backlog Month".
Then refer to the following measure:
Measure = IF ( SELECTEDVALUE ( 'Table 2'[Backlog Month] ) IN CALCULATETABLE ( DISTINCT ( 'Table'[Backlog Month] ), ALLEXCEPT ( 'Table', 'Table'[Invoice Number] ) ), "Y", "N" )Here is my test file for your reference.
It is a different approach to the above solution but seems to solve the problem too!, I will explore a little more to understand it better but thank you very much for your contribution!
