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

[hanan_ak]

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 (Text and monthly data) but most important ones are 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.

 

[AlanSidman]

Cannot manipulate data in a picture. Suggest you supply us with a file uploaded with enough sample data to evaluate all types of situations.

 

[hanan_ak]

Cannot manipulate data in a picture. Suggest you supply us with a file uploaded with enough sample data to evaluate all types of situations.

Thank you for your reply.

Please see the attached file.

 

[AlanSidman]

crossposted: https://www.myonlinetraininghub.com...d-custom-column-to-have-forecast-value#p27938

Please read: https://excelguru.ca/a-message-to-forum-cross-posters/

 

[vletm]

hanan_ak
Please, reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[hanan_ak]

hanan_ak
Please, reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

I Apologise for cross-posting and will avoid doing this in future.

Below is the explanation and solution file attached. The solution I have got from myonlinetraininghub.com by Riny van Eekelen.

"Data is nothing more than the "TransformColumnType" step renamed to something more friendly. I used the step name later on when I do a Merge. but now I changed after seeing your file, to make it more dynamic.
Then the Count step is a manually added step that counts the number of columns int he table and deducts 1 to not count the first column. Since your table has 9 text columns you need to deduct 9 here.
And the grouping need to be done on column that contains the unique identifier for each row, so I changed the Name column as a n example."