Excel Forum

How and where to calculate an order backlog history

peter warburton

Member

Members

Forum Posts: 17

Member Since:
January 8, 2021

Offline

February 10, 2023 - 2:11 am

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

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

February 10, 2023 - 7:44 am

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

peter warburton

Member

Members

Forum Posts: 17

Member Since:
January 8, 2021

Offline

February 11, 2023 - 1:34 am

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

peter warburton

Member

Members

Forum Posts: 17

Member Since:
January 8, 2021

Offline

February 17, 2023 - 12:38 am

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

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

February 18, 2023 - 3:03 am

Hello,

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

Br,
Anders

peter warburton

Member

Members

Forum Posts: 17

Member Since:
January 8, 2021

Offline

February 22, 2023 - 1:18 am

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