Creating a field for beginning of project

[DWig]

Hello all,

I have a PowerPivot model set up several tables. One is series of jobs, another is all of the costs broken out by line item (each is associated with a job). Each cost has a financial period associated with it. There is a certain cost code (call it XYZ) that is associated with the beginning of the job. Most of these have only one cost with this cost code, but some have more than one.

The English for the equation I'm trying to get to is "The earliest financial period associated with Cost Code XYZ for Job A." This could be in its own table with a list of the jobs, or anywhere else I can run in my powerpivot table. This seems like the most intuitive place to put it, but I can build this model to accommodate a variety of solutions.

I'm a real neophyte with DAX. Just got the relationships working yesterday, and I'm trying to put this piece together today.

Any and all help, links to resources useful for issues like this, etc. is appreciated.

Thanks,
DWig

 

[MD610]

Could you give a little more detail on the structure of your model?

For example, could you provide some sample data for each table or at least the names of the fields in each table to get a better understanding of hte DAX you need?

 

[DWig]

Certainly.

Table JobCostDetail has a bunch of columns, but the relevant ones for this equation with some samples:
[FinancialPeriod] [Job_Key] [CostCode] [Amount]
3/1/2007 1823 700 155
4/1/2007 5648 700 231
3/1/2007 1823 200 162
5/1/2007 9845 200 203
5/1/2007 1823 700 120
2/1/2007 6512 700 95
3/1/2007 6512 200 72
3/1/2007 9845 700 20

Table Job has a bunch of columns as well, but the relevant ones with some sample data are as follows:
[Job_Key] [Project] [PlanTemplateKey]
1823 9 River
5648 9 Stream
9845 12 River
6512 12 River


So what I'm trying to get is a value for the earliest financial period associated with cost code 700. This will only be used as a beginning marker. The other costs matter, I'm just trying to get a beginning date assigned to each Job_Key.
Example:
[Job_Key] [EarliestDateCostCode700]
1823 3/1/2007
5648 4/1/2007
9845 3/1/2007
6512 3/1/2007

This will then be used as the columns in a pivot table relating costs by PlanTemplateKey and Project.



Does that make sense?

 

[DWig]

After some more searching, I found the following on another forum from a few years back:

Maximum & Minimum Dates based on Condition

That shows how to do part of what I'm trying using an array function in Excel. It would just be adding in another condition where the cost code has to equal 700 to be included in the minimum range. However, arrays take up a crapload of memory, I'm not loading the data into excel (linking directly from a SQL server), and I'm pretty sure there are some other issues regarding excel array syntax/powerpivot DAX syntax.

I'll keep hammering away at it here, any insight would still be very much appreciated.

 

[MD610]

So let me know if this is just a consequence of a small sample set, but it looks to me like Cost Code 700 will always have the earliest date of the Job.

If that's the case, then you can just use Job_Key from Job in your pivot rows and use this simple measure in your values:

EarliestDateCostCode700:=MIN(JobCostDetail[FinancialPeriod])

This should show your the earliest date for the Job which looks like it will always be Cost Code 700.

If it isn't always 700, then you could try wrapping the measure above in a CALCULATE():

EarliestDateCostCode700:=CALCULATE(MIN(JobCostDetail[FinancialPeriod]), JobCostDetail[CostCode] = 700)

 

[DWig]

Mike,

Thank you for your help. This is getting me started toward understanding what's going on. When I add that calculated column to the JobCostDetail table, I get an error saying "Column 'CostCod' cannot be found or may not be used in this expression."

I also need to add something to the min function to further filter it by job. Something like JobCostDetail[Job_Key]= (the job key of the current row).

I really appreciate your help.

DWig

 

[MD610]

My equations were intended to be used as Measures as opposed to Calculated Columns. When you are new to DAX, there is a tendancy to favor Calculated Columns over Measures in situations where either might work. This isn't really right or wrong, however, once your comfort level with DAX increases, Measures are normally much more powerful and flexible.

If you want a Calculated Column in JobCostDetail that just tells you the earliest period of the project, then you would use something like this:

=CALCULATE(MIN([FinancialPeriod]),
               FILTER(JobCostDetail,
                         JobCostDetail[Job_Key] = EARLIER(JobCostDetail[Job_Key])
                         )
                    )

 

[DWig]

Mike,

Thanks a ton for all your help. The Calculated Column in JobCostDetail is working great for the earliest cost input. The last bit of the puzzle is to have it filter by cost code 700. I tried to add it in to the code you set up as follows. I got the same error as before: "Column 'CostCode' cannot be found or may not be used in this expression."

I really appreciate your help, and if you don't have any more time to devote to this, I understand. You've been more than helpful.


Code:
=CALCULATE(MIN([FinancialPeriod]), FILTER(JobCostDetail, JobCostDetail[Job_Key] = EARLIER(JobCostDetail[Job_Key])), JobCostDetail[CostCode]=700) )</pre>

 

[MD610]

No problem...its breaking up the monotony of writing out requirement docs.

I think you are close and just have a minor parentheses issue. You want the cost code test to be the 3rd arguement of CALCULATE() which I think you were trying to do:

=CALCULATE(MIN([FinancialPeriod]),
                       FILTER(JobCostDetail,
                                JobCostDetail[Job_Key] = EARLIER(JobCostDetail[Job_Key])
                                ),
                       JobCostDetail[CostCode] = 700
                    )

 

[DWig]

This is fantastic. Works perfectly. Thank you!