Power Query : Add a custom column to get YTD value and Custom column to have Forecast value

[hananak]

Dear All,

I would really appreciate your help.

I have a file; the data is imported via connection only in Power Query. I have many columns but most importantly the monthly columns. I want to have a custom column for YTD, which will sum up all these columns. Then I want to have another YTD column where it will divide the YTD value by the no of months I have and multiply by 12.

So, suppose we have two months data (April and May).

April = £1,000
May - £3,000

I want a custom column for YTD and the result should be = £4,000
Another custom column for Forcast and the result should be = (£4,000/2) x 12 = £24,000

and when I have in the next month 3 months data (April, May and June)

It should sum up all three months, and for the forecast, it should divide by 3 and multiply by 12 automatically.

Your help would be really appreciated.

 

[jdellasala]

@jdellasala, I'm smiling. Don't ask me why
Seems we have a different reading on the need. My take: the division by 2 is only because there are numbers filled out for the first two months and the year starts in April. For me the calendar used here is a fiscal year that's a quarter behind.

Glad you're smiling!
You have a good point, but what we both need is a clearer picture of what @hananak needs! I suppose that if the formula given is actually an AVERAGE and not a SUM, it would make more sense, but then dividing by 12 shouldn't be needed.
We'll have to wait and see what if any response we get!

 

[GraH]

I believe it's rather clear, no? See below in the quote. That's exactly what I do each month for a lineair forecast. It's a sum, not an average. It's a widespread approach to estimate a year end value. I stopped telling people they can use forecasting function btw.

and when I have in the next month 3 months data (April, May and June)

It should sum up all three months, and for the forecast, it should divide by 3 and multiply by 12 automatically.

Your help would be really appreciated.

 

[jdellasala]

I believe it's rather clear, no? See below in the quote. That's exactly what I do each month for a lineair forecast. It's a sum, not an average. It's a widespread approach to estimate a year end value. I stopped telling people they can use forecasting function btw.

Yeah, see that now. But my M code skill just doesn't cut it!