Hi all,
I am looking to create a year over year backlog database that I can graph and forecast. I have been working on it for a while but I can't seem to get it exactly where I want it with accurate numbers. This was originally created in Excel but it was complicated and outdated and I need to create a new version to make it more visual friendly so I would like to be able to put in on graphs and charts. I would like to be able to chart the total in each month and have that go back a few years to see where we have slower shipping times or where we have the highest backlog totals.
So the problem, say I have 2 dates, the request date and the actual ship date. If the order is past the request date then in backlog the price should show up, assuming the order has not been shipped already. If the request date is later than today, then the price should be put in future backlog, which means for us that the order is not late but this is how much in $$$ that is waiting to be shipped.
Another thing- I would like to be able to change the date to any day. I have created a calendar table that works and I also created a slicer to to reference that in dax formulas.
Right now my nonworking Backlog formula is:
Backlog= IF( AND('Data'[Actual Ship Date]>= [Slicer Date], 'Data'[Request Date]< [Slicer Date]), 'Data'[Price], 0)
Future Backlog = IF(AND('Data'[Order Date]<[Slicer Date], AND('Data'[Request Date]> [Slicer Value], 'Data'[Actual Ship Date]>= [Slicer Date])), 'Data'[Ext Price], 0)
The problems I am having: I am not getting the right total for the backllog, I would like to be able to change the slicer value(whatever date that I would like to display) to get the backlog price at that exact time. For example if I wanted to know what the backlog total amount was on March 1st, 2019, it would only show as 22,000. On March 1st, 2019, there would also be 6,000 in Future Backlog because there was an order placed on that date. The two orders placed 5/5/2019 and 6/25/2019 would not be included in future backlog because the order date is after the slicer date. Lastly, the slicer date is not dynamic and I do not know if that can be changed because any date I choose, the totals in powerbi do not change, it just filters out anything after the date I choose.
Order Date | Request Date | Actual Ship Date | Price | Backlog Price | Future Backlog Price |
12/15/2017 | 1/1/2018 | | 10,000 | 10,000 | 0 |
1/15/2018 | 3/5/2018 | | 12,000 | 12,000 | 0 |
9/1/2018 | 1/15/2019 | 1/10/2019 | 5,000 | 0 | 0 |
1/10/2019 | 2/10/2019 | 2/10/2019 | 8,000 | 0 | 0 |
3/1/2019 | 3/20/2019 | | 6,000 | 6,000 | 0 |
5/5/2019 | 6/15/2019 | 6/5/2019 | 14,000 | 0 | 0 |
5/5/2019 | 7/1/2019 | | 10,000 | 0 | 10,000 |
6/25/2019 | 8/15/2019 | | 15,000 | 0 | 15,000 |
Reference Date | Total Price | Current Backlog | Total Future Backlog |
6/20/2019 | 55,000 | 28,000 | 25,000 |
Thank you so much if you have suggestions, I am just lost. If there is another/ better way to do this I would love to hear it, I am just so sick of putting in dates that don't give me what I need. Thanks again!
@jdbuchanan71 @Zubair_Muhammad @Sean @tex628 @MFelix @Greg_Deckler
