PowerPivot: Need Help Creating a Prior Year Revenue measure w/o a calendar table

[jgbiii91]

Hello,

I need assistance creating a PY Revenue measure without a calendar table.

Prior Year Revenue is the calculated measure I need to create.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales Year[/TD]
[TD]Revenue[/TD]
[TD]Account ID[/TD]
[TD]Prior Year Revenue[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]$1234[/TD]
[TD]11231[/TD]
[TD]$36[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]$123[/TD]
[TD]213[/TD]
[TD]$165[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]$754[/TD]
[TD]505[/TD]
[TD]$1324[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]$36[/TD]
[TD]11231[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]$165[/TD]
[TD]213[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]$54[/TD]
[TD]13[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]$1324[/TD]
[TD]505[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Are you looking to do it with a calculate column or a measure?

Assuming your table is named Revenues

You can do it via a calculated column like this:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Revenue] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALLEXCEPT</span><span class="Parenthesis" style="color:#969696"> (</span><br>        Revenues,<br>        Revenues[Account ID]<br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span>,<br>    Revenues[Sales Year] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Sales Year] <span class="Parenthesis" style="color:#969696">)</span> - <span class="Number" style="color:#EE7F18">1</span><br><span class="Parenthesis" style="color:#969696">)</span><br>




Via a measure, one solution can be:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Revenue] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">CALCULATETABLE</span><span class="Parenthesis" style="color:#969696"> (</span><br>            Revenues,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Sales Year] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br>        Revenues[Sales Year] = <span class="Keyword" style="color:#0070FF">MAX</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Sales Year] <span class="Parenthesis" style="color:#969696">)</span> - <span class="Number" style="color:#EE7F18">1</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

the DAX may vary depending on how you want the calculation to behave at the total level

 

[jgbiii91]

May seem simple to you, but this is brilliant! Worked like a charm.

 

[VBA Geek]

Np. Actually if your DAX version supports variables, this is a better solution:

=<br><span class="Keyword" style="color:#0070FF">VAR</span> <span class="Variable" style="color:#49b0af">MxYr</span> =<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MAX</span><span class="Parenthesis" style="color:#969696"> (</span> Revenues[Sales Year] <span class="Parenthesis" style="color:#969696">)</span><br><span class="Keyword" style="color:#0070FF">RETURN</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span> [TotalRevenue], Revenues[Sales Year] = <span class="Variable" style="color:#49b0af">MxYr</span> - <span class="Number" style="color:#EE7F18">1</span> <span class="Parenthesis" style="color:#969696">)</span><br>

but, as the previous version, the PY value for Account 13 is missing

If you want it to show up, then you need to create this small model:

and use this measure (this time you slice the data in the pivot table by the dimension tables):

=<br><span class="Keyword" style="color:#0070FF">VAR</span> <span class="Variable" style="color:#49b0af">MxYr</span> =<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MAX</span><span class="Parenthesis" style="color:#969696"> (</span> Years[Sales Year] <span class="Parenthesis" style="color:#969696">)</span><br><span class="Keyword" style="color:#0070FF">RETURN</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span> [TotalRevenue], Years[Sales Year] = <span class="Variable" style="color:#49b0af">MxYr</span> - <span class="Number" style="color:#EE7F18">1</span> <span class="Parenthesis" style="color:#969696">)</span><br>