Calculate backlog over past time

[patrickmlsk]

Hi there!

I have a table containing all orders and specific dates related to that order. In simple words there is a processing date, a production date, a shipping date and a billing date. The order goes through different stages, if the stage is complete, the date is set into the table. So each order has an order date from the beginning with all other dates being blank. Over the passage of time the other dates will be set with the last date being the billing date. If the billing date is set, the order is complete. 

Here is a excel representation of my table:

I need to calculate different backlogs for each stage. An order backlog, processing backlog, production backlog, shipping backlog and a billing backlog. So the order backlog sums all orders that have no billing date. The processing backlog sums all orders that have no processing date. The prodicton backlog sums all orders that have a processing date, but no production date. The shipping backlog sums all orders that have a production date, but no shipping date. The billing backlog sums all orders that have a shipping date but no billing date.

I already got a report that shows the backlogs as of today, just with simple visual filtering.

However, I now want to show the backlogs overtime in a line graph. So that you can see the variation of the backlogs. 

I was playing with the betweendates DAX-function but simply did not find any solution. 

Thank you!

[patrickmlsk]

So maybe someone has an idea?

[Anonymous]

Hi @patrickmlsk ,

As checked your description, the whole flow is as follow: Order open--> Process--> Production-->Shipping --> Billing. And the related date will be filled after each stage be completed. Did you want to get the number of orders in each stage as below? Whether my understanding is correct or not?

order backlog=count(all orders)
processing backlog=count(order with only order date) 
production backlog=count(order with order date and processing date) 
shipping backlog=count(order with order date ,processing date and production date )  
billing backlog=count(order with order date ,processing date ,production date and shipping date) 

Best Regards

[patrickmlsk]

Hi @Anonymous 

thank you for your response. Your assumptions are correct. You can also see what I mean in the pbix that I included in my latest response. 

Calculating these numbers for today is easy, but visualising them historically in a line graph is what despairs me.. Maybe you have an idea?

[Anonymous · Accepted solution]

Hi @patrickmlsk ,

I have updated your sample pbix file, please check whether that is what you want.

1. Create a date table

2. Create some measures to get the related backlogs

Best Regards

[patrickmlsk]

So maybe someone has an idea?

[amitchandak]

@patrickmlsk , Not very clear. But you need a date table, Join to all these dates and one join will active, other will be inactive which you will activate using is the userelationship

https://www.youtube.com/watch?v=e6Y-l_JtCq4

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

You need to check blank date using isblank(Table[Date])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[patrickmlsk]

Thank you for your response! The date table with a userelationship could work. However, I still struggle to get the measures correct. 

I included a very light pbix with the data and an example how I did show the backlogs with visual filtering. 

Here you can find the pbix: https://drive.google.com/file/d/1w9cdV6QJ6AU_0bq1xJzCs7R9NViAUwtF/view?usp=sharing