Forum

How and where to ca...
 
Notifications
Clear all

How and where to calculate an order backlog history

6 Posts
2 Users
0 Reactions
454 Views
(@peterwarburton)
Posts: 17
Eminent Member
Topic starter
 

Hi,

I need help with a concept - making a calculation of order backlog value for every day in the date calendar to highlight seasonal or long-term trends in order backlog value.

Each order in my sales database has a created date and a shipped date and of course the order is in backlog between these dates. So where do i put the calculated field that would show for every day in the DateCalendar the sum of orders in backlog on that day?

I feel this should be a very simple thing to solve but somehow can't wrap my head around the necessary logic and where the DAX calculation should "reside".

Thanks & Regards,

Peter Warburton

 
Posted : 10/02/2023 12:11 pm
Anders Sehlstedt
(@sehlsan)
Posts: 970
Prominent Member
 

Hello,

Seems to me that every order with a created date but no shipped date is to be summed, which should be doable to setup. If you can upload a sample file we can check what needs to done.

Br,
Anders

 
Posted : 10/02/2023 5:44 pm
(@peterwarburton)
Posts: 17
Eminent Member
Topic starter
 

Hello Anders,

That would only give the backlog for today and only for orders that haven't shipped.

What I am trying to achieve is a view of the backlog evolving over time (or how quickly are we getting orders out the door) so I need to calculate the backlog value for every date in my date calendar (2017 to 2023), with the filter criteria being sum the order $ values if the Date calendar date falls between the order created date and the order shipped date.

Hope this clarifies my goal,

 

Regards,

Peter

 
Posted : 11/02/2023 11:34 am
(@peterwarburton)
Posts: 17
Eminent Member
Topic starter
 

Hi,

I've partially solved it. I put this calculated column in my Date Calendar:

Backlog = CALCULATE(SUM('Order Summary'[Order Amount]),FILTER('Order Summary','DateCalendar'[Date]>='Order Summary'[Created Date]&&'DateCalendar'[Date]<'Order Summary'[Ship Date]))
 
I can then graph the monthly, quarterly, yearly average backlog in a "Backlog trend" visual.
 
I'm not sure if the DateCalendar is the right place for the calculation though because I can't filter my "backlog trend" visual for different sales regions.
 
If I put this calculation in my Order Summary table it will only calculate a backlog for the dates there were orders so a monthly average would not be correct, and I still wouldn't be able to filter by sales region I think.
 
Peter
 
Posted : 17/02/2023 10:38 am
Anders Sehlstedt
(@sehlsan)
Posts: 970
Prominent Member
 

Hello,

I would gladly try to help, but there is no sample file I can work with.

Br,
Anders

 
Posted : 18/02/2023 1:03 pm
(@peterwarburton)
Posts: 17
Eminent Member
Topic starter
 

Thank you for your offer Anders but the data is confidential so I would have to create a synthetic replica for you to look at. I've partially solved the problem with the calculation mentioned above, so I will leave it there and cogitate on improvements.

 

Regards,

Peter

 
Posted : 22/02/2023 11:18 am
Share: