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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.