Getting Job To Date Totals while looking at a period of data

In the typical Job Cost Reporting world you can easily create SQL Views / Stored Procedures (or create a report) that can return both Job To Date values along with period values for another date range.  Something like this:

Code:

Job, Cost Distribution,
Cost Type,
UM,
Budgeted Cost,
Budgeted Quantity,
Period Cost,
Period Quantity,
Period Unit Cost
JTD Cost,
JTD Quanity
JTD Unit Cost,
Variance between JTD and Period Cost,
Variance between JTD and Period Quantity,
Variance between JTD and Period Unit Cost 

But in the tabular BI world I am not certain how to have these values show up in a pivot table or BI visualization.  My current Fact table is essentially every job cost transaction so it is at the most granular level holding Company, Job, Cost Distribution, UM, Cost Type, Date, Budget Cost, Budget Quantity, Actual Cost, Actual Quantity.  The Budget values only have a value if the entry was updating the budget and the Actual values hold actual cost for each transaction.  If you don't restrict by date you basically have your Budgeted Quantity / Cost and Job To date Cost / Quantity.

I can generate a Pivot table or chart and not restrict by date and I can get all the Job To Date totals. 

But what if I want to look at a snapshot like last quarter or yesterday or last week.  I can indeed filter that data and get the period values, but now I don't know how to have the Job To Date values show up for comparison purposes.

I am guessing maybe a DAX formula (if using Excel Powerpivot or SQL Tabular Model), but I am at a loss.

So in the BI world how do you get Job To Date totals while also viewing the data for a period of time?

Here are some example graphs we would want to show for a single Job.  Let's say we want to show the 10 worst performing cost distributions.  

• I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Cost, Period Cost, Job To Date Cost.
  
• I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Unit / Cost, Period Unit / Cost, Job To Date Unit / Cost.
  
• I would want a graph to show the cost distribution and the variance between JTD and Budget and Period and Budget.
  

As you can see it all stems around seeing JTD values vs Period Values.

Hope the above makes sense.

Thanks,

Greg

I have found that the following helped me on my JTD Totals using DAX

JTDCost:=CALCULATE(SUM(Fact_JobCostDetail[ActualCost]),ALL(Fact_JobCostDetail[JobPhaseCTID]))

So essentially I created a sum calculation that summed the Actual Cost across all Job Phases and Cost Type.

I haven't fully tested it to see what happens as I play with various dimensions or if I change the field used in the ALL section, but will report back.