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