DAX: How far can I leverage the CALCULATE formula?

[vega805]

I have a fact table with expense data for many raspberry ranches. I need to report total expenses both by cycle and aggregated, this will be done in a dashboard that is already surfacing production information.</SPAN>

Each ranch has many cycles, a cycle is established by a start and stop date, cycles can be different (start and stops) for each ranch. </SPAN>

Formerly and for other commodities I’ve been using the CALCULATE formula in aggregating expenses, but now the dimension f cycle is throwing me... In brainstorming a solution for summing to a cycle, I’m not sure how I could leverage CALCULATE, if at all as there may be another solution.</SPAN>

Is a calculated measure for each ranch/cycle combination the right path of thinking? -> could I =CALCULATE (SUM(tbl_expenses), ranch=112233,dates= BETWEEN 1/1/13 AND 6/30/13, etc. ) or this wouldn’t work? I have a table that document the start and stop date for a ranch - is there a lookup feature in DAX to refer to join ranchNumber and retrieve such values? Or am I thinking about this all wrong? </SPAN>

 

[penguinpower]

I have a fact table with expense data for many raspberry ranches. I need to report total expenses both by cycle and aggregated, this will be done in a dashboard that is already surfacing production information.

Each ranch has many cycles, a cycle is established by a start and stop date, cycles can be different (start and stops) for each ranch.

Formerly and for other commodities I’ve been using the CALCULATE formula in aggregating expenses, but now the dimension f cycle is throwing me... In brainstorming a solution for summing to a cycle, I’m not sure how I could leverage CALCULATE, if at all as there may be another solution.

Is a calculated measure for each ranch/cycle combination the right path of thinking? -> could I =CALCULATE (SUM(tbl_expenses), ranch=112233,dates= BETWEEN 1/1/13 AND 6/30/13, etc. ) or this wouldn’t work? I have a table that document the start and stop date for a ranch - is there a lookup feature in DAX to refer to join ranchNumber and retrieve such values? Or am I thinking about this all wrong?

It's usually not the right path of thinking, since it just brings a ****load of work in the future for every cycle, and I'm guessing the goal of most our projects is to minimize work and let computers do it for us.

Now more specifically, first thing you need is a table with information about cycles. Let's say CycleData table consisting of CycleID, RanchID, StartDate, EndDate. You probably already have your ExpenseData table: ExpenseID, RanchID, ExpenseDate, ExpenseAmount. Connect these by RanchID for reporting purposes, connection is not needed for calculation.

Now your formula to get an Expense by Cycle calculated column looks something like

=CALCULATE(SUM(ExpenseData[ExpenseAmount]),FILTER(ExpenseData, ExpenseData[ExpenseDate]>=CycleData[StartDate]&&ExpenseData[ExpenseDate]<=CycleData[EndDate]&&ExpenseData[RanchID]=CycleData[RanchID]))

I'm an Excel guy, not a BI guy. There is probably a smarter, more memory efficient day to do this, using all the cool advanced functions of PowerPivot. But for me, this should work, I understand it easily and I can debug it easily. Some of the stuff here is probably superfluous (RanchID filtering could probably be solved with a relationship), but I can understand this without understanding the engine behind PowerPivot.

So to deconstruct:

=CALCULATE(SUM(ExpenseData[ExpenseAmount]), - pretty self-evident, this is what you're interested in
,FILTER(ExpenseData, - If this is where your data is, that's the sheet you wanna filter
ExpenseData[ExpenseDate]>=CycleData[StartDate] - Using a calculated column, you make sure that there is only one date selected for StartDate (calc. column always uses current row as context. My proxy for understanding is that if I would use #thisrow in excel table, I use calc. column in powerpivot).
&&ExpenseData[ExpenseDate]<=CycleData[EndDate] - && means both conditions have to be true, you could use multiple filters in calculate to get the same effect, this is just less effort
&&ExpenseData[RanchID]=CycleData[RanchID])) - You are only interested in expenses connected to this ranch

Now in your report if your sheet looks like

RanchID, CycleID,SUM(ExpenseByCycle)

You should get what you need.

Let me know if I missed something, I didn't test this in a dummy model, so maybe even the syntax isn't 100% correct, but the logic should be clear.

Cheers,

B

 

[vega805]

Hi B, thank you kindly for the in depth explanation, conceptually this makes sense. Geeze, I never considered the FILTER function on this.

You’d fool me that you’re not a“BI” guy, it’s the custom approach to the solution that counts and this sounds/looks logical. I need to sketch this out and consider my current cycle table – its a bit different and I may have to redesign. Hopefully I can give this a test run later tonight and I'll update

V

 

[vega805]

Took me a while to get back to this, other facets of project.

So I adapted your solution into my model. The problem I'm running into now is relating the expense table with the cycle table, I get an error that Cycle doesn't have a relationship, so this breaks --> ExpenseData[ExpenseDate]>=CycleData[StartDate]. Both tables point to a common table, ranchTable <-- Is there an expression im missing such as RELATED() to link tables? I cant think of one., or is there something im missing completely?

 

[vega805]

link to diagram of model

 

[penguinpower]

Took me a while to get back to this, other facets of project.

So I adapted your solution into my model. The problem I'm running into now is relating the expense table with the cycle table, I get an error that Cycle doesn't have a relationship, so this breaks --> ExpenseData[ExpenseDate]>=CycleData[StartDate]. Both tables point to a common table, ranchTable <-- Is there an expression im missing such as RELATED() to link tables? I cant think of one., or is there something im missing completely?

It shouldn't matter for the calculation, since this solution does not require a connection... I'll take a look at the diagram when I get home, the company firewall is blocking dropbox...

 

[penguinpower]

For this to work, you should put it into the cycle table, since that is where attributes are defined. so expense[date]<=cycle[date_end] and so on...

but you can also flip it the other way, and add to the expense table something like this (shortened for space, add your exact column names):

CALCULATE(VALUES(Cycle Details[CycleID])FILTER(cycle Detail,cycledetail[datestarted]<expense[date]&&cycle[dateended]>expense[date]&&expense[ranchnumber]=cycle[ranchnumber]))

Cheers,

b

 

[vega805]

For this to work, you should put it into the cycle table, since that is where attributes are defined. so expense[date]<=cycle[date_end] and so on...

but you can also flip it the other way, and add to the expense table something like this (shortened for space, add your exact column names):

CALCULATE(VALUES(Cycle Details[CycleID])FILTER(cycle Detail,cycledetail[datestarted]<EXPENSE[DATE]&AMP;&AMP;CYCLE[DATEENDED]>expense[date]&&expense[ranchnumber]=cycle[ranchnumber]))

Cheers,

b

b,

Thank you for your follow-up on this.

Still cant get it to work.

Also, Im not quite sure Im understanding; this is a calculated column, not a calculated field? How would I include a calculated column in the cycle table?

To put this:
</SPAN>
=CALCULATE(SUM(oracle[TOTAL]),FILTER(oracle, oracle[date]>=Cycle_Detail[expStartDate]&&oracle[date]<=Cycle_Detail</SPAN>
[expEndDate]&&oracle[LOT]=Cycle_Detail[ranchNumber]))

in the cycle table seems slightly backwards, or its definitely a new application of the CALCULATE formula and I just can’t get it to work. Do you mean for this to be a measure or field, versus a column?

Also, since Im use to things being related, I dont understand how its referring to the oracle (expense) table in the formula.</SPAN>

("oracle" is an exported expense table, same as before)
</SPAN>

 

[vega805]

"Still cant get it to work."


Pardon me for being vague what I had meant in terms of "not working" was getting this regardless of where I put it, cycle expense, it doesn't seem to be able to determine the first argument

The value for column 'expStartDate' in table 'Cycle_Detail' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

 

[vega805]

It shouldn't matter for the calculation, since this solution does not require a connection... I'll take a look at the diagram when I get home, the company firewall is blocking dropbox...

I solved this, thank you again for the direction d,

V