Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bearpoz79
Helper I
Helper I

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.

bearpoz79_0-1610570710949.png

Data set      
Insert DateSourceCustomerCoverageShip DateBookings Calc End DateFiscal Yr-Wk
12/5/2020Weekly BacklogJohnny Cupcakes500 12/4/20202020-48
12/12/2020Weekly BacklogJohnny Cupcakes400 12/11/20202020-49
12/19/2020Weekly BacklogJohnny Cupcakes600 12/18/20202020-50
1/1/2021Weekly BacklogJohnny Cupcakes300 12/31/20202020-52
1/8/2021Weekly BacklogJohnny Cupcakes500 1/7/20212021-01
1/15/2021Weekly BacklogJohnny Cupcakes2000 1/14/20212021-02
1/13/2021ShipmentsJohnny Cupcakes10012/7/202012/7/20202020-49
1/13/2021ShipmentsJohnny Cupcakes10012/14/202012/14/20202020-50
1/13/2021ShipmentsJohnny Cupcakes30012/21/202012/21/20202020-51
1/13/2021ShipmentsJohnny Cupcakes10012/28/202012/28/20202020-52
1/13/2021ShipmentsJohnny Cupcakes1001/4/20211/4/20212021-01
1/13/2021ShipmentsJohnny Cupcakes6001/13/20211/13/20212021-02
1/13/2021BacklogJohnny Cupcakes1500 1/13/20212021-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 WkBegining BacklogShipmentsBookingsEnding Backlog
2020-495001000400
2020-50400100300600
2020-516003000300
2020-52300100300500
2021-0150010016002000
2021-0220006001001500

 

 

 

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 2
v-lionel-msft
Community Support
Community Support

Hi @bearpoz79 ,

 

v-lionel-msft_0-1610677189663.png

What is the mathematical calculation logic of [Begining Backing] and [Ending Backing]?

 

Best regards,
Lionel Chen

If 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...  

 

Capture8.PNGCapture7.PNGCapture6.PNGCapture5.PNGCapture4.PNGCapture2.PNGCapture.PNGCapture3.PNG

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors