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

H

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Please see the picture of the data file in power query for your reference.
 

Attachments

  • Monthly Datas.jpg
    Monthly Datas.jpg
    61.2 KB · Views: 43
Upvote 0
One possibility
Book1
CDEFGHIJKLMNO
2Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22
3a82403428136--------
4b25481306406--------
5c16829181309--------
6d39335344086--------
7e596614973--------
8f221187374171--------
Sheet1

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Something", type text}, {"jan/22", Int64.Type}, {"feb/22", Int64.Type}, {"mrt/22", Int64.Type}, {"apr/22", Int64.Type}, {"mei/22", Int64.Type}, {"jun/22", Int64.Type}, {"jul/22", Int64.Type}, {"aug/22", Int64.Type}, {"sep/22", Int64.Type}, {"okt/22", Int64.Type}, {"nov/22", Int64.Type}, {"dec/22", Int64.Type}}),
    Add_Record = Table.AddColumn(#"Changed Type", "Record", each
[
Mths = Table.LastN(Record.ToTable(_),12),
YTD = List.Sum(Mths[Value]),
LFC = YTD/List.PositionOf(Mths[Value], 0)*12
], type record ),
    #"Expanded Record" = Table.ExpandRecordColumn(Add_Record, "Record", {"YTD", "LFC"}, {"YTD", "LFC"})
in
    #"Expanded Record"
Book1
ABCDEFGHIJKLMNO
1Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22YTDLFC
2a824034281360000000010493147
3b254813064060000000010473141
4c16829181309000000008492547
5d393353440860000000012723816
6e596614973000000003471041
7f221187374171000000009532859
Table1
 
Upvote 1
Solution
GraH said:
One possibility
Book1
CDEFGHIJKLMNO
2Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22
3a82403428136--------
4b25481306406--------
5c16829181309--------
6d39335344086--------
7e596614973--------
8f221187374171--------
Sheet1

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Something", type text}, {"jan/22", Int64.Type}, {"feb/22", Int64.Type}, {"mrt/22", Int64.Type}, {"apr/22", Int64.Type}, {"mei/22", Int64.Type}, {"jun/22", Int64.Type}, {"jul/22", Int64.Type}, {"aug/22", Int64.Type}, {"sep/22", Int64.Type}, {"okt/22", Int64.Type}, {"nov/22", Int64.Type}, {"dec/22", Int64.Type}}),
    Add_Record = Table.AddColumn(#"Changed Type", "Record", each
[
Mths = Table.LastN(Record.ToTable(_),12),
YTD = List.Sum(Mths[Value]),
LFC = YTD/List.PositionOf(Mths[Value], 0)*12
], type record ),
    #"Expanded Record" = Table.ExpandRecordColumn(Add_Record, "Record", {"YTD", "LFC"}, {"YTD", "LFC"})
in
    #"Expanded Record"
Book1
ABCDEFGHIJKLMNO
1Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22YTDLFC
2a824034281360000000010493147
3b254813064060000000010473141
4c16829181309000000008492547
5d393353440860000000012723816
6e596614973000000003471041
7f221187374171000000009532859
Table1
Click to expand...
Thank you very much. This is exactly what I wanted.
 
Upvote 0
@GraH - Great solution! I liked it so much I used RANDBETWEEN to generate a month of values away from the source table:
Book1
H
11273
12364
13250
14113
15399
16260
Sheet1
Cell Formulas
RangeFormula
H11:H16H11=RANDBETWEEN(MIN(Table1[[Jan-22]:[Apr-22]]),MAX(Table1[[Jan-22]:[Apr-22]]))

and in the Stupid Excel Tricks category was able to copy and paste the values into each month. However when I had all 12 months filled came up with a strange result which led me to look at all the values. It looks like there's a problem with the calculation for the LFC Column:
Book1
ABCDEFGHIJKLMNOPQ
1SomethingJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22YTDLFC(YTD/2)*12
2a82403428136298103434397137373110902,991-35,89217,946
3b254813064063703331734092002242092133,178-38,13619,068
4c168291813098533569435802294063022,790-33,48016,740
5d39335344086270147312398348365314713,497-41,96420,982
6e5966149731962524211471372323401522,224-26,68813,344
7f2211873741712902243114382961792303513,272-39,26419,632
YtdReport
Cell Formulas
RangeFormula
Q2:Q7Q2=(SUM(YtdReport[@[Jan-22]:[Dec-22]])/2)*12

As I filled each month's column, the LFC did not follow the formula specified. That said, I think the formula is flawed and should be taking the AVERAGE of the months reported. I worked out a VERY inelegant solution that resorted to Transposing the original table and creating two new tables for the YTD and LFC values, appending the 3 tables and then transposing them back. I'm sure there's a MUCH better way to do it!
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Forum

Power Query : Add a custom column to get YTD value and Custom column to have Forecast value
www.myonlinetraininghub.com www.myonlinetraininghub.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
jdellasala said:
@GraH - Great solution! I liked it so much I used RANDBETWEEN to generate a month of values away from the source table:
Book1
H
11273
12364
13250
14113
15399
16260
Sheet1
Cell Formulas
RangeFormula
H11:H16H11=RANDBETWEEN(MIN(Table1[[Jan-22]:[Apr-22]]),MAX(Table1[[Jan-22]:[Apr-22]]))

and in the Stupid Excel Tricks category was able to copy and paste the values into each month. However when I had all 12 months filled came up with a strange result which led me to look at all the values. It looks like there's a problem with the calculation for the LFC Column:
Book1
ABCDEFGHIJKLMNOPQ
1SomethingJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22YTDLFC(YTD/2)*12
2a82403428136298103434397137373110902,991-35,89217,946
3b254813064063703331734092002242092133,178-38,13619,068
4c168291813098533569435802294063022,790-33,48016,740
5d39335344086270147312398348365314713,497-41,964
YtdReport
Click to expand...

jdellasala said:
@GraH - Great solution! I liked it so much I used RANDBETWEEN to generate a month of values away from the source table:
Book1
H
11273
12364
13250
14113
15399
16260
Sheet1
Cell Formulas
RangeFormula
H11:H16H11=RANDBETWEEN(MIN(Table1[[Jan-22]:[Apr-22]]),MAX(Table1[[Jan-22]:[Apr-22]]))

and in the Stupid Excel Tricks category was able to copy and paste the values into each month. However when I had all 12 months filled came up with a strange result which led me to look at all the values. It looks like there's a problem with the calculation for the LFC Column:
Book1
ABCDEFGHIJKLMNOPQ
1SomethingJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22YTDLFC(YTD/2)*12
2a82403428136298103434397137373110902,991-35,89217,946
3b254813064063703331734092002242092133,178-38,13619,068
4c168291813098533569435802294063022,790-33,48016,740
5d39335344086270147312398348365314713,497-41,96420,982
6e5966149731962524211471372323401522,224-26,68813,344
7f2211873741712902243114382961792303513,272-39,26419,632
YtdReport
Cell Formulas
RangeFormula
Q2:Q7Q2=(SUM(YtdReport[@[Jan-22]:[Dec-22]])/2)*12

As I filled each month's column, the LFC did not follow the formula specified. That said, I think the formula is flawed and should be taking the AVERAGE of the months reported. I worked out a VERY inelegant solution that resorted to Transposing the original table and creating two new tables for the YTD and LFC values, appending the 3 tables and then transposing them back. I'm sure there's a MUCH better way to do it!
Click to expand...
I was perhaps a bit hasty. I use a DAX formula for a lineair forecast at work. I saw a similarity with PQ in the thread but did overlook when there is no zero value. In that case the ytd is the lfc.
 
Upvote 0
Quick fix
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Something", type text}, {"jan/22", Int64.Type}, {"feb/22", Int64.Type}, {"mrt/22", Int64.Type}, {"apr/22", Int64.Type}, {"mei/22", Int64.Type}, {"jun/22", Int64.Type}, {"jul/22", Int64.Type}, {"aug/22", Int64.Type}, {"sep/22", Int64.Type}, {"okt/22", Int64.Type}, {"nov/22", Int64.Type}, {"dec/22", Int64.Type}}),
    Add_Record = Table.AddColumn(#"Changed Type", "Record", each [
Mths = Table.LastN(Record.ToTable(_),12),
YTD = List.Sum(Mths[Value]),
Div = List.Max({1,List.PositionOf(Mths[Value], 0)}),
LFC = if Div = 1 then YTD else YTD / Div*12
]),
    #"Expanded Record" = Table.ExpandRecordColumn(Add_Record, "Record", {"YTD", "LFC"}, {"YTD", "LFC"})
in
    #"Expanded Record"
Book1
ABCDEFGHIJKLMNO
1Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22YTDLFC
2a82403428136824034281368240342813631473147
3b25481306406254813064062548130640631413141
4c16829181309168291813091682918130925472547
5d39335344086393353440863933534408638163816
6e59661497359661497359661497310411041
7f22118737417122118737417122118737417128592859
Table2
 
Upvote 0
GraH said:
Quick fix
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedNulls = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Jan-22", "Feb-22", "Mar-22", "Apr-22", "May-22", "Jun-22", "Jul-22", "Aug-22", "Sep-22", "Oct-22", "Nov-22", "Dec-22"}),
    ChangedType = Table.TransformColumnTypes(ReplacedNulls,{{"Something", type text}, {"Jan-22", Int64.Type}, {"Feb-22", Int64.Type}, {"Mar-22", Int64.Type}, {"Apr-22", Int64.Type}, {"May-22", Int64.Type}, {"Jun-22", Int64.Type}, {"Jul-22", Int64.Type}, {"Aug-22", Int64.Type}, {"Sep-22", Int64.Type}, {"Oct-22", Int64.Type}, {"Nov-22", Int64.Type}, {"Dec-22", Int64.Type}}),
    AddedYTD = Table.AddColumn(ChangedType, "YTD", each [#"Jan-22"]+[#"Feb-22"]+[#"Mar-22"]+[#"Apr-22"]+[#"May-22"]+[#"Jun-22"]+[#"Jul-22"]+[#"Aug-22"]+[#"Sep-22"]+[#"Oct-22"]+[#"Nov-22"]+[#"Dec-22"], Int64.Type),
    AddedLFC = Table.AddColumn(AddedYTD, "LFC", each ([YTD]/2)*12)
in
    AddedLFC
Book1
ABCDEFGHIJKLMNO
1Somethingjan/22feb/22mrt/22apr/22mei/22jun/22jul/22aug/22sep/22okt/22nov/22dec/22YTDLFC
2a82403428136824034281368240342813631473147
3b25481306406254813064062548130640631413141
4c16829181309168291813091682918130925472547
5d39335344086393353440863933534408638163816
6e59661497359661497359661497310411041
7f22118737417122118737417122118737417128592859
Table2
Click to expand...
I hate challenging you - typically you're a Power Query Guru! However when all 12 months have data the LFC value is not what was asked. That is the sum of the 12 months divided by 2 and then multiplied by 12. The formula makes no sense to me, but that's what was asked.
While the use of adding a record is cool, this is a more direct approach:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedNulls = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Jan-22", "Feb-22", "Mar-22", "Apr-22", "May-22", "Jun-22", "Jul-22", "Aug-22", "Sep-22", "Oct-22", "Nov-22", "Dec-22"}),
    ChangedType = Table.TransformColumnTypes(ReplacedNulls,{{"Something", type text}, {"Jan-22", Int64.Type}, {"Feb-22", Int64.Type}, {"Mar-22", Int64.Type}, {"Apr-22", Int64.Type}, {"May-22", Int64.Type}, {"Jun-22", Int64.Type}, {"Jul-22", Int64.Type}, {"Aug-22", Int64.Type}, {"Sep-22", Int64.Type}, {"Oct-22", Int64.Type}, {"Nov-22", Int64.Type}, {"Dec-22", Int64.Type}}),
    AddedYTD = Table.AddColumn(ChangedType, "YTD", each [#"Jan-22"]+[#"Feb-22"]+[#"Mar-22"]+[#"Apr-22"]+[#"May-22"]+[#"Jun-22"]+[#"Jul-22"]+[#"Aug-22"]+[#"Sep-22"]+[#"Oct-22"]+[#"Nov-22"]+[#"Dec-22"], Int64.Type),
    AddedLFC = Table.AddColumn(AddedYTD, "LFC", each ([YTD]/2)*12)
in
    AddedLFC
Had to add the ReplacedNulls step because any null values in the months columns resulted in a null value in the YTD column which makes no sense, but so it goes! The Resulting in this:
Book1
ABCDEFGHIJKLMNOPQ
1SomethingJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22YTDLFCFormula in Q2: =(SUM(YTD_LFC[@[Jan-22]:[Dec-22]])/2)*12
2a8240342813682403428136824034281363,14718,88218,882
3b2548130640625481306406254813064063,14118,84618,846
4c1682918130916829181309168291813092,54715,28215,282
5d3933534408639335344086393353440863,81622,89622,896
6e5966149735966149735966149731,0416,2466,246
7f2211873741712211873741712211873741712,85917,15417,154
YTD-LFC
Cell Formulas
RangeFormula
Q1Q1=_CellFormula(Q2)
Q2:Q7Q2=(SUM(YTD_LFC[@[Jan-22]:[Dec-22]])/2)*12

which uses the same data as your sample. I certainly admit, it is CLUNKY, but it is accurate.
 
Upvote 0
jdellasala said:
I hate challenging you - typically you're a Power Query Guru! However when all 12 months have data the LFC value is not what was asked. That is the sum of the 12 months divided by 2 and then multiplied by 12. The formula makes no sense to me, but that's what was asked.
While the use of adding a record is cool, this is a more direct approach:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedNulls = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Jan-22", "Feb-22", "Mar-22", "Apr-22", "May-22", "Jun-22", "Jul-22", "Aug-22", "Sep-22", "Oct-22", "Nov-22", "Dec-22"}),
    ChangedType = Table.TransformColumnTypes(ReplacedNulls,{{"Something", type text}, {"Jan-22", Int64.Type}, {"Feb-22", Int64.Type}, {"Mar-22", Int64.Type}, {"Apr-22", Int64.Type}, {"May-22", Int64.Type}, {"Jun-22", Int64.Type}, {"Jul-22", Int64.Type}, {"Aug-22", Int64.Type}, {"Sep-22", Int64.Type}, {"Oct-22", Int64.Type}, {"Nov-22", Int64.Type}, {"Dec-22", Int64.Type}}),
    AddedYTD = Table.AddColumn(ChangedType, "YTD", each [#"Jan-22"]+[#"Feb-22"]+[#"Mar-22"]+[#"Apr-22"]+[#"May-22"]+[#"Jun-22"]+[#"Jul-22"]+[#"Aug-22"]+[#"Sep-22"]+[#"Oct-22"]+[#"Nov-22"]+[#"Dec-22"], Int64.Type),
    AddedLFC = Table.AddColumn(AddedYTD, "LFC", each ([YTD]/2)*12)
in
    AddedLFC
Had to add the ReplacedNulls step because any null values in the months columns resulted in a null value in the YTD column which makes no sense, but so it goes! The Resulting in this:
Book1
ABCDEFGHIJKLMNOPQ
1SomethingJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22YTDLFCFormula in Q2: =(SUM(YTD_LFC[@[Jan-22]:[Dec-22]])/2)*12
2a8240342813682403428136824034281363,14718,88218,882
3b2548130640625481306406254813064063,14118,84618,846
4c1682918130916829181309168291813092,54715,28215,282
5d3933534408639335344086393353440863,81622,89622,896
6e5966149735966149735966149731,0416,2466,246
7f2211873741712211873741712211873741712,85917,15417,154
YTD-LFC
Cell Formulas
RangeFormula
Q1Q1=_CellFormula(Q2)
Q2:Q7Q2=(SUM(YTD_LFC[@[Jan-22]:[Dec-22]])/2)*12

which uses the same data as your sample. I certainly admit, it is CLUNKY, but it is accurate.
Click to expand...
@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 starting in April. For me the calendar used here is a fiscal year that's a quarter behind.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
305
alex78
A
H
  • Question Question
Need help in Unpivoting Data in Power Query
Replies
1
Views
218
alansidman
A
D
  • Question Question
Power Query - add custom formula to data set
Replies
1
Views
765
CSmith
CSmith
H
  • Question Question
Year to Date Column (POWER Query)
Replies
2
Views
370
GraH
GraH
H
  • Question Question
Loading and Combining data from folder not loading all columns - missing last column
Replies
1
Views
839
hananak
H

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top