Running calculations

[rileybloke]

Hi,
I have powerpivot model that includes 4 tables of data.

1. Product Item Key table

• Item Number
• Stock on hand (now)

2. Date Key Table

• Year
• Week Number

3. Customer Order Table

• Item Number
• Year
• Week
• Order Quantity

4. Supplier Delieries Table

• Item Number
• Year
• Week
• Delivery Quantity

I need help with a dax calculation or measure to sum the order quantities for each item for each year/week, and the same for supplier deliveries.
And then present it on a Pivot table running across years/weeks, but as a running calculation starting from the STOCK OH(table1), and deducting by customer order, and adding supplier orders, this will then show if any items will become out of stock.

Ive attached what I have already done, but I need to insert another column showing the running total using the running calculation above.
Any advise or help would be great, ive been battling with it for ages, reading forums, watching youtube vids etc.

 

[miguel.escobar]

Did Javier's post help you?
Running calculations

 

[rileybloke]

Thanks, nearly, but Javiers date granularity is monthly, I need weekly. Is it possible to total by week, or as I already have week number and year in each of the tables, how can I sum total customer order quantity for week 1 ( there are many order in week 1) ?

 

[miguel.escobar]

Could you publish a copy of your workbook so I can take a look at your dates table and your fact table?

Also, when you say weekly, you want the running total to start all over at the 1st week of the following year or you just want to know the rolling total?
You could just use the TOTALYTD if you are using the "default" week numbers. If you have specific week identification like, for example, that your week starts on Fridays instead of Mondays then a customized CALCULATE or SUMX could do the job.

Hope this helps!

 

[rileybloke]

Hi Miguel, here is my workbook


https://www.dropbox.com/l/DUqveRCJpjGFhJqj

I need the rolling calculations to begin using the ACTUAL OH from the ITEM key table. This is my starting inventory figure, then I need to sum all the customer orders for each week, and deduct from the ACTUAL OH, then sum all the supplierDelivery qty's for the same week, and add them to the rolling calculations. The calculations will then roll week on week, and highlight which week we would run short of stock.

Huge thanks in advance

 

[miguel.escobar]

Hey Riley!

This is a pretty interesting case but is not difficult and I trust in you! =) I know that you can do it and I'm going to help you by giving you a hint:

First a note: - you should get a full calendar table, your date key has gaps between dates and wouldn't work for time intelligence functions in the event that you'd like to use.


Neverthelesss! What you need to do is create a calculated column on your dates table that will be your helper column. Create it using the following format:

=YEAR&IF(WEEK<10,"0"&WEEK,WEEK)+0
(Note that you'll have to do this by creating several calc columns)
step 1: IF(WEEK<10,"0"&WEEK,WEEK&"")
step 2: YEAR&Step1
step 3: Step2 + 0

and in the end you'll have something like 201001 that would be week 1 of 2010 which is less than 201002, which is week 2 of 2010...you probably have the idea already

then it should be easy. You can either use SUMX or CALCULATE to create a measure and use a FILTER that should say something among these lines:
"filter the table by the values that are <= to the current value that I have in my column labels"

try it out and let me know! I trust in you

 

[miguel.escobar]

https://dl.dropbox.com/u/54063091/Risk%20Sheet.xlsx

This is an example of the measure.

hope it helps.

Best,
Miguel

 

[abhishek0990]

nice information. thank you for sharing it.

thanks
packersm

 

[rileybloke]

Brilliant! thanks im sailing now

 

[miguel.escobar]

the actual formula is showing something like a cummulative over time (based on the new column) which I believe is what you're looking for. Let me know how your final formula looks like