Getting Job To Date Totals while looking at a period of data
Page 1 of 1
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 : 20150615
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 : 20150615
Similar topics
» Calculating Totals and Date Durations
» Variable period data in a single periodic snapshot fact table
» scd2 effective date, end date data type
» Date Dimension with "Latest Data"
» Slow changing fact data with an effective date
» Variable period data in a single periodic snapshot fact table
» scd2 effective date, end date data type
» Date Dimension with "Latest Data"
» Slow changing fact data with an effective date
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

