Forum Discussion
Bookings Calc Help
Hi, looking for a better way to build this calc out..
This formula is to create a quarterly weekly bookings number with one slicer. I have been able to do it using two slicers but wondering if there is a better way.
About the date:
There is only one data set, and it refreshes nightly with shipments, backlog and a weekly backlog snapshot (which is taken Saturday AM an represents the end of the fiscal week). Below is a sample of the data.
Note the Bookings Calc End Date;
=IF(Source="Weekly Backlog",Insert Date -1 ,IF(Source="Backlog",Insert Date,IF(Source="Shipments",Ship Date,"")))
this calc allows the Weekly backlog to represent the ending backlog for a given week.
| Data set | ||||||
| Insert Date | Source | Customer | Coverage | Ship Date | Bookings Calc End Date | Fiscal Yr-Wk |
| 12/5/2020 | Weekly Backlog | Johnny Cupcakes | 500 | 12/4/2020 | 2020-48 | |
| 12/12/2020 | Weekly Backlog | Johnny Cupcakes | 400 | 12/11/2020 | 2020-49 | |
| 12/19/2020 | Weekly Backlog | Johnny Cupcakes | 600 | 12/18/2020 | 2020-50 | |
| 1/1/2021 | Weekly Backlog | Johnny Cupcakes | 300 | 12/31/2020 | 2020-52 | |
| 1/8/2021 | Weekly Backlog | Johnny Cupcakes | 500 | 1/7/2021 | 2021-01 | |
| 1/15/2021 | Weekly Backlog | Johnny Cupcakes | 2000 | 1/14/2021 | 2021-02 | |
| 1/13/2021 | Shipments | Johnny Cupcakes | 100 | 12/7/2020 | 12/7/2020 | 2020-49 |
| 1/13/2021 | Shipments | Johnny Cupcakes | 100 | 12/14/2020 | 12/14/2020 | 2020-50 |
| 1/13/2021 | Shipments | Johnny Cupcakes | 300 | 12/21/2020 | 12/21/2020 | 2020-51 |
| 1/13/2021 | Shipments | Johnny Cupcakes | 100 | 12/28/2020 | 12/28/2020 | 2020-52 |
| 1/13/2021 | Shipments | Johnny Cupcakes | 100 | 1/4/2021 | 1/4/2021 | 2021-01 |
| 1/13/2021 | Shipments | Johnny Cupcakes | 600 | 1/13/2021 | 1/13/2021 | 2021-02 |
| 1/13/2021 | Backlog | Johnny Cupcakes | 1500 | 1/13/2021 | 2021-02 |
The result i am trying to get is below.
Note the column "Bookings" is a formula: =Ending backlog - (Beginning backlog - shipments for the week)
| Fiscal Wk | Begining Backlog | Shipments | Bookings | Ending Backlog |
| 2020-49 | 500 | 100 | 0 | 400 |
| 2020-50 | 400 | 100 | 300 | 600 |
| 2020-51 | 600 | 300 | 0 | 300 |
| 2020-52 | 300 | 100 | 300 | 500 |
| 2021-01 | 500 | 100 | 1600 | 2000 |
| 2021-02 | 2000 | 600 | 100 | 1500 |
My work around is that i have two date tables both with a relationship to the Bookings Calc End Date field, whereas one of the date tables is an inactive relationship. Then in the visual, i have two weekly slicers and users have to select each Starting week and Ending Week.
The bookings calc is a formula and it works, but the users (and I) want to be able to just select one week only and have it autopopulate the starting backlog numbers.
Any reccomendations for how to model this different?
thank you,
Josh
2 Replies
Hi Anonymous ,
What is the mathematical calculation logic of [Begining Backing] and [Ending Backing]?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel, Thank you for reaching out. To help explain my situation i am attaching the pbi images (i would share the Pbi file, but not sure how to do that) , The first image shows how the formula works when the two date slicers are correctly chosen for start and end date. But my goal is to have one slicer do this. Also, if i select more than one week, the formula doesn't work because it does not sum the total shipments for all the periods of the range. Thank you for your time and help.
Below are images of the relationship, table in pbi, and the formulas for the fields you asked for.
The last image is an excel file showing the answer and on row 33, the answer if we select the start date as the first week and the end date as the last/current week. FOr some reason the backlog calcs are correct, but the shipments is not, it does not subtotal all the transactions for the range...
