PowerPivot DAX Measure to Dynamically Calculate Rolling 12 Months

B

bravo1563

New Member
Joined
Dec 25, 2013
Messages
20
Hello
I have a unique problem, each month I have a new 12 month forecast. What I need to do using a DAX measure is sum last period of each official forecasts with a 1 month lag for each official forecast. In other words in the below example if I'm filtered on MAR 2017 I need the measure to sum all of the bolded red values which equates 554. Please note this is not in a regular calendar months but in a unique fiscal dating. But if we can write the measure in regular calendar time intelligence I should be able to convert to proper time periods. Any help would be greatly appreciated, thanks!!


[TABLE="width: 691"]
<tbody>[TR]
[TD="align: center"]Official Forecast[/TD]
[TD="align: center"]DEC[/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[TD="align: center"]APR[/TD]
[TD="align: center"]MAY[/TD]
[TD="align: center"]JUN[/TD]
[TD="align: center"]JUL[/TD]
[TD="align: center"]AUG[/TD]
[TD="align: center"]SEP[/TD]
[TD="align: center"]OCT[/TD]
[TD="align: center"]NOV[/TD]
[/TR]
[TR]
[TD="align: center"]MAR 2016[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]58[/TD]
[/TR]
[TR]
[TD="align: center"]APR 2016[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]73[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]16[/TD]
[/TR]
[TR]
[TD="align: center"]MAY 2016[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JUN 2016[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]79[/TD]
[/TR]
[TR]
[TD="align: center"]JUL 2016[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]AUG 2016[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]SEP 2016[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]OCT 2016[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]NOV 2016[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]84[/TD]
[/TR]
[TR]
[TD="align: center"]DEC 2016[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]31[/TD]
[/TR]
[TR]
[TD="align: center"]JAN 2017[/TD]
[TD="align: center"]18
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]54[/TD]
[/TR]
[TR]
[TD="align: center"]FEB 2017[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]69[/TD]
[/TR]
[TR]
[TD="align: center"]MAR 2017[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]76[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
I'm sure this can be done, but I don't really understand the scenario. What do the columns refer to? What are all the othe numbers that are not bold red?

I'm pretty sure the table needs to be restructured. I would have a long thin table like this

scenario. Period. Forecast
Mar 16. Apr 16. X
Mar 16. May 16. y
Mar. 16. Jun. 16. Z

etc

thne join the period to a calendar table and use the scenario to filter the scenario you are looking at.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Percentage by Year instead of total
Replies
1
Views
244
EvonS
E
S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
196
shansakhi
S
K
  • Question Question
Help with looping through an entire lists in Power Query
Replies
2
Views
519
JGordon11
JGordon11
S
  • Question Question
PowerBI Measure to calculate Sales Goals with Reports To: structure.
Replies
1
Views
266
Matt Allington
M
X
  • Question Question
PowerPivot DAX Reallocation of Site Amounts to Other Sites
Replies
0
Views
385
Xandria619
X

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

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