Pivot Table and Adding Quarters

[whynot]

I created a pivot table using the data from the table below. The data in Column A is formatted as a date. In a pivot table i want to add the quarters so that i get a total for each year. I used Group Selection, but it does not provide the option to group in quarters. There are no options provided. I am at a loss. What am i doing wrong. I truly appreciate any help that is provided.


Pivot Table

Row Labels	Sum
3/31/2017	204619154​
6/30/2017	197276126​
9/30/2017	194609498​
12/31/2017	216482232​
3/31/2018	222583456​
6/30/2018	210632201​
9/30/2018	207991580​
12/31/2018	238866314​
3/31/2019	238179001​
6/30/2019	237819738​
9/30/2019	226252943​
12/31/2019	252882437​
3/31/2020	242151784​
6/30/2020	179472280​
9/30/2020	220072404​
12/31/2020	245920543​
3/31/2021	234502816​
6/30/2021	285959266​
9/30/2021	298892104​
12/31/2021	318317870​
3/31/2022	263571771​
6/30/2022	259058115​
9/30/2022	249956297​
Grand Total	5446069930​

Table

3/31/2017​

204,619,154​

256,576,370​

280,283,949​

68,844,334​

1,541,668,929​

116,907,007​

213,766,884​

15,651,999​

83,909,380​

92,991,981​

74,733,825​

22,477,463​

6/30/2017​

197,276,126​

248,814,483​

285,120,698​

71,763,992​

1,690,741,727​

143,278,814​

205,389,910​

16,451,922​

94,020,662​

100,838,362​

75,716,882​

26,520,600​

9/30/2017​

194,609,498​

243,666,694​

285,285,896​

71,219,106​

1,715,541,121​

155,697,257​

211,488,965​

17,070,063​

99,773,171​

100,045,137​

77,477,380​

27,935,104​

12/31/2017​

216,482,232​

277,862,870​

302,188,149​

74,945,458​

1,871,330,602​

164,703,822​

220,204,314​

17,725,927​

93,400,917​

108,521,253​

77,496,828​

27,690,014​

3/31/2018​

222,583,456​

269,453,967​

300,175,047​

71,102,636​

1,627,531,752​

153,466,018​

234,127,107​

17,564,377​

93,663,154​

96,139,223​

78,142,894​

27,642,353​

6/30/2018​

210,632,201​

266,024,593​

300,556,776​

71,221,942​

1,724,045,828​

169,333,744​

223,664,095​

17,793,565​

107,820,223​

101,316,667​

76,860,623​

33,972,622​

9/30/2018​

207,991,580​

257,247,047​

307,873,494​

67,401,083​

1,785,869,727​

177,174,184​

233,446,594​

18,267,224​

109,145,142​

105,895,392​

80,276,943​

33,530,165​

12/31/2018​

238,866,314​

301,340,021​

315,064,834​

69,100,641​

1,908,853,545​

197,929,132​

240,555,028​

19,069,644​

103,131,448​

115,481,025​

80,910,163​

32,253,861​

3/31/2019​

238,179,001​

286,581,408​

298,178,923​

63,116,601​

1,660,974,672​

179,856,464​

237,069,692​

17,815,721​

101,557,994​

98,657,676​

80,388,295​

31,088,321​

6/30/2019​

237,819,738​

285,944,000​

300,696,270​

64,663,981​

1,822,596,971​

206,375,462​

235,995,725​

18,427,692​

114,934,823​

106,043,352​

75,590,136​

37,566,094​

9/30/2019​

226,252,943​

269,211,570​

296,787,013​

58,845,719​

1,822,861,454​

210,712,877​

232,196,579​

18,002,860​

114,617,867​

105,519,710​

82,191,855​

36,466,618​

12/31/2019​

252,882,437​

315,418,774​

304,122,675​

60,297,459​

1,941,702,279​

227,721,609​

231,302,765​

18,469,957​

105,679,323​

113,186,112​

81,391,645​

33,261,574​

3/31/2020​

242,151,784​

297,830,056​

312,405,506​

52,766,248​

1,713,542,632​

224,425,817​

243,726,361​

19,354,652​

94,799,382​

103,940,288​

83,616,425​

29,677,986​

6/30/2020​

179,472,280​

234,892,700​

284,458,360​

39,099,366​

1,599,973,079​

243,198,322​

220,053,704​

18,840,460​

76,950,500​

103,135,005​

71,637,950​

29,955,013​

9/30/2020​

220,072,404​

255,848,780​

296,668,398​

42,419,638​

1,593,998,321​

239,632,038​

234,257,292​

19,428,531​

95,866,503​

109,481,953​

79,210,519​

35,355,587​

12/31/2020​

245,920,543​

290,898,989​

300,687,851​

44,949,830​

1,743,104,083​

252,227,615​

235,818,947​

19,708,273​

85,373,785​

114,502,018​

77,800,954​

32,964,760​

3/31/2021​

234,502,816​

277,273,042​

281,818,341​

43,132,349​

1,571,645,826​

230,295,654​

237,095,640​

18,218,376​

88,918,031​

99,819,994​

73,586,054​

33,200,274​

6/30/2021​

285,959,266​

339,748,643​

296,784,926​

53,662,561​

1,722,422,146​

257,945,159​

257,109,498​

20,365,315​

106,265,780​

109,829,349​

76,815,563​

40,867,891​

9/30/2021​

298,892,104​

363,336,454​

310,882,414​

55,188,600​

1,852,599,362​

269,559,851​

263,966,544​

21,587,041​

117,441,738​

115,983,488​

80,865,665​

44,245,425​

12/31/2021​

318,317,870​

393,511,412​

313,942,750​

60,334,217​

1,964,319,131​

293,946,493​

262,049,448​

22,205,801​

110,649,401​

123,702,814​

78,257,083​

43,363,788​

3/31/2022​

263,571,771​

316,028,036​

283,545,970​

47,846,243​

1,632,489,293​

248,419,009​

249,384,457​

19,749,691​

103,776,520​

102,244,172​

70,998,658​

39,961,649​

6/30/2022​

259,058,115​

309,149,693​

288,922,299​

48,617,573​

1,808,692,409​

280,815,750​

248,462,886​

19,899,551​

119,262,827​

110,220,401​

73,782,335​

48,300,901​

9/30/2022​

249,956,297​

291,299,987​

285,520,156​

46,506,163​

1,813,627,223​

275,227,622​

247,429,975​

19,735,732​

120,349,302​

111,465,194​

75,004,706​

48,676,633​

 

[jdellasala]

PLEASE use XL2BB. The easiest way is to add a Calendar table and load both tables into the Data Model. The Calendar table will hold dates from Jan 1 of the year earliest date in your data to at least Dec 31 of the year of the latest date in your table. In the Data Model add columns as needed for Quarters (using whatever month you want to start with), and create a relationship between the Date field in the Calendar and the Date field in the Data and hide the Date field in the Data so that you only use the Calendar's date. Here's the M Code to create a generic Calendar Table:

let
   //courtesy of AccessAnalytic.com.au


    Today = Date.From(DateTime.LocalNow() ),  

   // Change start date to begining of year
    StartDate= #date(2019, 1, 1),        
    
   //see advanced editor for note on alternative hardcoding EndDate method
    YearsInFuture = 0,
    EndDate = Date.EndOfYear(Date.AddYears(Today,YearsInFuture )),

    // Or comment out the 2 lines above replace with manual End Date below.. use end of year   
    //EndDate = #date(2021, 12, 31), 
    #"Pointless Step Added To Break Comment In Advanced Editor" = "",


    //set this as the last month number of your fiscal year : June = 6, July =7 etc
    MonthNumberForEndFinancialYear = 6,

   // Change to Day.Sunday or Day.Tuesday etc to impact the sort order number so you can then display your days in your visuals in the preferred way
    FirstDayOfWeek = Day.Monday,   



    DateList = {Number.From(StartDate)..Number.From(EndDate)},
    

    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month Number", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day", each Text.Start( Date.DayOfWeekName([Date]),3), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],FirstDayOfWeek), Int64.Type),
    #"Inserted Quarter Number" = Table.AddColumn(#"Inserted Day of Week", "Quarter", each Date.QuarterOfYear([Date]),Int64.Type),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Quarter Number",{{"Quarter", type text}, {"Year", type text}}),
    #"Added Quarter Name" = Table.AddColumn(#"Changed Type2", "Custom", each "Q"&[Quarter]),
    #"Removed Quarter Name" = Table.RemoveColumns(#"Added Quarter Name",{"Quarter"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Quarter Name",{{"Custom", "Quarter"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Quarter", type text}}),
    #"Added YYQQ" = Table.AddColumn(#"Changed Type3", "YY-QQ", each Text.End( [Year],2) & "-"& [Quarter]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added YYQQ",{{"YY-QQ", type text}, {"Year", Int64.Type}}),
    #"▶ DatesSinceTodayFields" = #"Changed Type4",
    DateToday = Today,
    DaysAgo = Table.AddColumn(#"▶ DatesSinceTodayFields", "Days Since Today", each Duration.Days([Date] -  DateToday), Int32.Type),
    MonthsAgo = Table.AddColumn(DaysAgo, "Months Since Today", each ([Year] * 12 + [Month Number]) - (Date.Year(DateToday ) * 12 + Date.Month(DateToday )), Int32.Type),
    YearsAgo = Table.AddColumn(MonthsAgo, "Years Since Today", each [Year] - Date.Year(DateToday ), Int32.Type),
    #"◀ Dates Since Today" = YearsAgo,
    #"▶Financial Year Calcs" = #"◀ Dates Since Today",
    #"FY Month Number" = Table.AddColumn(#"▶Financial Year Calcs", "Financial Month Number", each if [Month Number] > MonthNumberForEndFinancialYear  then [Month Number]-MonthNumberForEndFinancialYear  else 12-MonthNumberForEndFinancialYear+[Month Number]),
    #"Changed Type1" = Table.TransformColumnTypes(#"FY Month Number",{{"Financial Month Number", Int64.Type}}),
    #"Financial Year End" = Table.AddColumn(#"Changed Type1", "Financial Year End", each if [Financial Month Number] <=12-MonthNumberForEndFinancialYear  then [Year]+1 else [Year]),
    #"Fiancial Year Start" = Table.AddColumn(#"Financial Year End", "Financial Year Start", each [Financial Year End] - 1, type number),
    #"Changed Type5" = Table.TransformColumnTypes(#"Fiancial Year Start",{{"Financial Year End", type text}, {"Financial Year Start", type text}}),
    #"Added Financial Year Range" = Table.AddColumn(#"Changed Type5", "Financial Year", each Text.End( [Financial Year Start],2) & "-" & Text.End([Financial Year End],2)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Financial Year Range",{"Financial Year End", "Financial Year Start"}),
    // To work out Financial Quarter
    #"DivideFinancialMonth by 3" = Table.AddColumn(#"Removed Columns1", "Financial Qtr Number", each [Financial Month Number] / 3, type number),
    #"Rounded Up to get Quarter" = Table.TransformColumns(#"DivideFinancialMonth by 3",{{"Financial Qtr Number", Number.RoundUp, Int64.Type}}),
    #"Added Financial Quarter" = Table.AddColumn(#"Rounded Up to get Quarter", "Financial Quarter", each "FQ-"&Text.From([Financial Qtr Number])),
    #"Removed FYQ Helper" = Table.RemoveColumns(#"Added Financial Quarter",{"Financial Qtr Number"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Removed FYQ Helper",{{"Financial Quarter", type text}, {"Financial Year", type text}}),
    #"◀ Financial Year Calcs" = #"Changed Type6",
    #"Renamed Sort Columns" = Table.RenameColumns(#"◀ Financial Year Calcs",{{"Month Number", "Month Number for sort"}, {"Financial Month Number", "Financial Month Number for sort"}, {"Day of Week", "Day of Week for sort"}})
in
    #"Renamed Sort Columns"

I did not create that. I'm pretty sure I got it from this YouTube Video. Files available there.

In the query above, just adjust the Start Date (2nd step - just enter the date manually), and the Years In The Future (3rd step, again manually and years into the future from today). Once it's pulled into the Data Model, you will have to adjust Text columns like the Month Name to sort by the Month Number. Likewise for the Quarter if the Quarter doesn't start in January.

I just used this earlier today and it was a real time saver. I don't know if many use this trick, but from the Queries and Connections list (not in the PQ Editor) you can Right Click on a Query and select Export to Connection File and save the query to use in another Workbook (or Power BI file). It will be saved to the My Data Sources folder in your user profile. What that folder is under depends on OS, whether or not you're using OneDrive, etc. That's how I got to this code so easily.

Back to your question, once you've loaded the Data and the Calendar to the Data Model and prepared everything, you can list the date as needed along with any ancillary columns in the Calendar. Since you'll only be using the Date field from the Calendar, and the relationship between that date and the date in your Data table are related, the values in columns will be filtered properly and Q values can be used as needed in the Rows. (I actually haven't tried it with Columns!).

Hope that helps.