Measures dependant on another measure.

I have a fact table that has sales data for products.

I have to fields in my fact table, WantDateId and InvoicedDateId which are integer representation of a date that join to the time dimension table.

I also have measures for feet, pounds, sales dollars, and material cost.

I have a bunch of dimensions for product type, sales rep, territory, shipped date…

I my cube I want to show a picture of sales for three things broken down over the dimensions.

What has been ordered

What has been invoiced

What is outstanding (backlog).

Most important is the current month where some will be shipped and some will be backlog.

The key here is that if the invoiceDateId = 0 then it has not shipped.

In order for aggregations to work, do I have to add columns to my fact table?This would mean I would need three of each measure.Is there a way to get this with calculated measures?

Hello. Here you have some links to design advices for your question.

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2000%20/KimballDT2MultipleTime.pdf

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2002/KimballDT37ModelingPipeline.pdf

http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT42Combining.pdf

http://www.kimballgroup.com/html/articlesArchitecture/articlesAdvancedFact.html

I am not sure of what you would like to accomplish but sales data and snapshots, like OrderStock are normally not included in the same fact table.

HTH

Thomas Ivarsson