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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Backlog evolution at month end

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))+0

 

with ), 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Backlog evolution at month end

I did not find the excel link.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Backlog evolution at month end

Thanks for letting me know. I have edited the post to include it.

Highlighted
Super User IV
Super User IV

Re: Backlog evolution at month end

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))+0

 

with ), 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Community Support
Community Support

Re: Backlog evolution at month end

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Helper II
Helper II

Re: Backlog evolution at month end

It works!!, thank you very much for your help!! 🙂

Highlighted
Helper II
Helper II

Re: Backlog evolution at month end

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!

Highlighted
Helper II
Helper II

Re: Backlog evolution at month end

@amitchandak When trying to import my real data, I found the following error:

 

Sheet1
Column 'Submission Date' in Table 'Sheet1' contains a duplicate value '10/17/2018' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
 
Both, Invoice Dates and Submission dates can have duplicated values (but never more than 1 value per invoice number).
 
I think that this error has to do with the joint 1to1 between the two tables (fields Date and Submission Date) but I dont undertand what this joint is used for.
 
Can you help me to figure it out?
 
Thanks in advance!
Highlighted
Super User IV
Super User IV

Re: Backlog evolution at month end

Double click on relation line and make relation Date table to invoice table 1 to Many and single direction and check.

 

When power bi do not see the duplicate date it create 1-1 relation.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors