My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home
Lost password?
sp_Search
Advanced Search
Advanced Search
Forum Scope



Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search

Please confirm you want to mark all posts read

Mark all topics read

sp_MobileMenu Actions
Avatar

New/Updated Topics

Select Forum

sp_TopicIcon
How and where to calculate an order backlog history
Avatar
peter warburton
Posts: 17
Level 0
February 10, 2023 - 2:11 am

1

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden
Posts: 870


Level 3
February 10, 2023 - 7:44 am

2

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

Avatar
peter warburton
Posts: 17
Level 0
February 11, 2023 - 1:34 am

3

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

Avatar
peter warburton
Posts: 17
Level 0
February 17, 2023 - 12:38 am

4

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
Avatar
Anders Sehlstedt
Eskilstuna, Sweden
Posts: 870


Level 3
February 18, 2023 - 3:03 am

5

Hello,

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

Br,
Anders

Avatar
peter warburton
Posts: 17
Level 0
February 22, 2023 - 1:18 am

6

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

Forum Timezone:
Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ivica Cvetkovski
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)

Devices in use: Desktop (6), Phone (5)

Forum Stats:
Groups: 3
Forums: 24
Topics: 6206
Posts: 27202
Member Stats:
Guest Posters: 49
Members: 31875
Moderators: 3
Admins: 4