Data Model to Table Calculation

[cmcreynolds]

Hello again MRExceleteers

Here is the result of a piece of my pivot table I created in Power Pivot. Now, the three right columns are all calculated fields (let's use ColA, ColB, ColC, and ColD to differentiate) summarized (aggregated, if you will) by the dates you see on the left.

Now I need a rate that involves one date AND the previous.

[TABLE="width: 367"]
<tbody>[TR]
[TD]1/14/2013
[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3/11/2013[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5/6/2013[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/26/2013
[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


For example:

The rate for 8/26/2013 is (72-35)/(45-9-0). I can get the numerator and denominator no problem, but how do I divide using the previous date as a calculated field?

I was thinking maybe making it a flat table and having a column to the right with simple "Excel" formulas, but I didn't know if it can be done in the data model as a calculated field.

Thoughts?

 

[scottsen]

Totally didn't understand (72-35)/(45-9-0), but let's just ignore that and move on

Your question boils down to "how do I write a filter that returns the prior date?" Not sure if you have a Calendar/Date table already? that would impact my answer.

=<br><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">HASONEVALUE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[Date] <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>[MyFreakyMath],<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span><span class="indent4">    </span>MyTable,<br><span class="indent8">        </span><span class="indent4">    </span>MyTable[Date]<br><span class="indent8">        </span><span class="indent8">        </span>= <span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> <span class="Keyword" style="color:#0070FF">MAX</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[Date] <span class="Parenthesis" style="color:#D0D0D0">)</span>, MyTable[Date] < <span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> MyTable[Date] <span class="Parenthesis" style="color:#D0D0D0">)</span> <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

Which is to say "Grab me the table row that is the biggest... of everything smaller than me"

 

[cmcreynolds]

Hey Scottsen -

I was just trying to use unique numbers as an example.

I've never used HASONEVALUE before - can I use that in a calculated field?

and the issue is the [myfreakymath] has the numerator of one date and the denominator from the previous.

 

[scottsen]

Ya, HASONEVALUE can be used anywhere DAX is valid. It' just to protect a call to VALUES() which likes to blow up if there are multiple rows passed to it (like in a grand total cell).

My calculation would be just one of the numerator/demoninator I guess... whichever one wants to find the prior date.