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

## 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:

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.

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

Hope the above makes sense.

Thanks,

Greg

- 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

**gsaunders**- Posts : 5

Join date : 2015-06-15

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

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.

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.

**gsaunders**- Posts : 5

Join date : 2015-06-15

Similar topics

» how to sort date in selenium

» How to parameterize with "today's date" with IDE

» bad date stories

» To pick current date from calender control in selenium ide.

» Date Picker in Webdriver?

» How to parameterize with "today's date" with IDE

» bad date stories

» To pick current date from calender control in selenium ide.

» Date Picker in Webdriver?

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum