Using Dim Date and results for various measures

Hello,

I am fairly new to BI, but fairly knowledgeable in the SQL world.

I have a Data Model with the following (simplified here):

1. Contract Fact view: Contains measures Original Contract Amount, Current Contract Amt, Billed Amount and Actual Cost.  It also contains 3 dates of importance: Actual Date, Post Date and Accounting Month.
   
2. Contract Dim view: Contains the Contract attributes.
   
3. Company Dim view: Contains the Company attributes.
   
4. Three Dim Date views (One for Actual Date, Post Date and Accounting Month)
   

I create a pivot table with Rows:

1. Company
   
2. Contract
   

And Values:

1. Original Contract Amount, Current Contract Amount, Billed Amount and Actual Cost.
   

At this level all looks GREAT!

Now... take it to where I want the break out for calendar year the following field are fine:

1. Billed Amt (Sum)
   
2. Actual Cost (Sum)
   

These look good as I am seeing the actual cost or billed amount for those periods of time (by year).

However these columns do not come out like we would want:

1. Original Contract Amount
   
2. Current Contract Amount.
   

Since the transactions in the Contract Fact table are 1 transaction for every hit to Contract you will only see one entry for a specific contract when it comes to Original Contract Amount and you may see 0 to many entries that increase the Contract Amount providing the Current Contract Amount.

So when the date is thrown into the Pivotable Rows you will see these two columns have values on the dates those entries showed up when the user wants to see the running total for these two fields over the date range provided.

So instead of only 2010 having the Original Contract Amount they want to see that amount over all the years... like a running total as well as the Current Contract Amount. 

One reason is they want to track backlog over time and the backlog if Current Contract Amount - Billed Amount leaving Backlog Amount.  And because of how the Current Contract Amount is breaking out the Backlog Amount will not calculate correctly.

Here is a visual:

Year 2011	Orig Contract Amt $11,793,773	Current Contract Amt $11,793,773	Billed Amt $1,388,341
2012	$0	$120,805	$3,561,359
2013	$0	$413,849	$3,179,614
2014	$0	$174,097	$3,397,154
2015	$0	$52,052	$1,350,072

Above you can see that the Original (1 time ) contract entry is $11,793,773 and you can see over the years there were increases to the contract (but you just see the amount it increased) and then you see how the Billed Amt breaks out over the years (which is what they do want to see).

They are wanting to see the Orig Contract Amt continue to flow down and see the Current Contract Amt act like a running total.  And of course at the level above (contract) they want the proper amount there as well.

Are they wanting something that just normally is not done here or is it just a matter of me adjusting the model for those values or using some DAX formula to achieve what they want to see?

Again I am just beginning in the BI world so trying to take in as much as I can as fast as I can on this sort of data analysis.

Thanks,

Greg

I don't know if the BI tool being used will determine if this is doable, but in Excel I just noticed the "Value Field Settings", "Show Value As" Tab a drop down letting me pick "Running Total In".

This looks like it may give the visualization wanted in the pivot table.  Now you lose the bolded "Total", but you now see it act like a running total.

But like I said I don't know if that is going to be a specific feature of the BI visualization tool or if there is a way to do something in the data model.

I didn't get any responses... but found a way that this should be done in Data Model and not rely on the BI Visual Tool.   Basically using a DAX formula within the tabular data model.

Based on info from daxpatterns website:

Code:

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Wanted to leave a solution in case someone was looking to do same thing.