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]
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]