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

jgbiii91

New Member
Joined
Jul 12, 2018
Messages
3
Hello,

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

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

Sales YearRevenueAccount IDPrior Year Revenue
2017$123411231$36
2017$123213$165
2017$754505$1324
2017$36112310
2016$1652130
2016$54130
2016$13245050

<tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Last edited:
Upvote 0
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

HapU6yd.png



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

AKMHh5O.png





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>


FXDUF4K.png
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top