Lag Forecast calculation

DAX DAX Calculations
1

Dear EDNA Experts,

I am trying to calculate Lag forecast numbers for each month by using DAX. Can anyone please help to sort this out?

Future Month Forecast
Forecast Submission Month Product 01-01-2021 01-02-2021 01-03-2021 01-04-2021 01-05-2021 01-06-2021 01-07-2021 01-08-2021 01-09-2021 01-10-2021
01-01-2021 A 133 120 128 94 127 126 94 80 141 94
01-02-2021 A 145 124 108 108 123 95 125 103 101
01-03-2021 A 103 150 144 90 127 135 137 103
01-04-2021 A 88 81 97 141 119 94 118
01-05-2021 A 118 105 129 140 88 126
01-06-2021 A 149 127 87 114 100
01-07-2021 A 112 124 100 125

My required answer like below,

Actual Month Product Lag 3 Forecast Lag 2 Forecast Lag 1 Forecast Lag 0 Forecast
01-01-2021 A - - - 133
01-02-2021 A - - 120 145
01-03-2021 A - 128 124 103
01-04-2021 A 94 108 150 88
01-05-2021 A 108 144 81 118
01-06-2021 A 90 97 105 149
01-07-2021 A 141 129 127 112

Example, For Apr’21, Lag 3 forecast for Product A is what was submitted during 3 months back ie, Jan’21 forecast submission for Apr’21.

Regards
Jamie

1 Like
2

Hi Jamie,

it would be really useful if you could provide your PBIX file please.

DJ

3

Hi @James_Thomas ,

I’ve created an excel file based on the data you show.

Then to calculate the Lag X Forecast I’ve used this kind of formula each time

image
image729×248 5.59 KB

The final result is that :
image

All the results aren’t shown because I’ve stopped the excel on April 2021 submission date.

Here is the files :
Book3.xlsx (9.7 KB)
Untitled4.pbix (48.3 KB)

I hope it will help you.

Best regards,
Joaly

3 Likes
4

Hi Joaly,

Thanks for this solution. It is working. :blush:

Regards
Jamie

2 Likes