Hi Ashish-
I'm working with mulitple time-series data tables with one master calendar to tie them together. I know that I need to use the default date heirarchy for quick measures to work but I have lost the default date heirarchy. I think the quick measure is having difficulty with the relationships that I have between mulitiple tables. To keep it simple I'll focus on the master date table "Calendar' and one of the data tables.
I generated a master date table with the following code:
Calendar = ADDCOLUMNS (
CALENDAR (DATE(1976,1,1), DATE(2021,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ), "YearQuarter", FORMAT ( [Date], "Q" ) & "Q" & FORMAT ([Date], "YYYY" )
)
Table of monthly sales data of "widgets", that I'd like to have year-over-year growth rates for:
| Date | North America Sales | Europe Sales | Japan Sales | ROW Sales | Total Sales |
| Jan-78 | $ 210,126,000 | $ 76,815,000 | $ 12,880,000 | $ 18,240,000 | $ 318,061,000 |
| Feb-78 | $ 218,159,000 | $ 85,662,000 | $ 15,675,000 | $ 18,388,000 | $ 337,884,000 |
| Mar-78 | $ 258,264,000 | $ 105,068,000 | $ 17,728,000 | $ 23,985,000 | $ 405,045,000 |
| Apr-78 | $ 235,842,000 | $ 92,800,000 | $ 19,630,000 | $ 20,167,000 | $ 368,439,000 |
| May-78 | $ 246,783,000 | $ 95,979,000 | $ 19,219,000 | $ 19,869,000 | $ 381,850,000 |
| Jun-78 | $ 290,285,000 | $ 103,225,000 | $ 22,611,000 | $ 23,516,000 | $ 439,637,000 |
| Jul-78 | $ 233,536,000 | $ 94,544,000 | $ 22,462,000 | $ 23,434,000 | $ 373,976,000 |
| Aug-78 | $ 246,130,000 | $ 83,272,000 | $ 23,302,000 | $ 25,805,000 | $ 378,509,000 |
| Sep-78 | $ 306,546,000 | $ 118,699,000 | $ 27,969,000 | $ 28,406,000 | $ 481,620,000 |
| Oct-78 | $ 265,833,000 | $ 97,437,000 | $ 23,971,000 | $ 27,685,000 | $ 414,926,000 |
| Nov-78 | $ 274,282,000 | $ 104,485,000 | $ 23,435,000 | $ 25,473,000 | $ 427,675,000 |
| Dec-78 | $ 337,543,000 | $ 120,704,000 | $ 30,590,000 | $ 33,682,000 | $ 522,519,000 |
| Jan-79 | $ 270,417,000 | $ 100,634,000 | $ 23,990,000 | $ 25,499,000 | $ 420,540,000 |
| Feb-79 | $ 294,360,000 | $ 107,914,000 | $ 25,294,000 | $ 26,003,000 | $ 453,571,000 |
| Mar-79 | $ 348,896,000 | $ 130,615,000 | $ 35,135,000 | $ 32,370,000 | $ 547,016,000 |
| Apr-79 | $ 319,159,000 | $ 116,238,000 | $ 29,214,000 | $ 28,927,000 | $ 493,538,000 |
| May-79 | $ 340,300,000 | $ 122,380,000 | $ 33,696,000 | $ 34,151,000 | $ 530,527,000 |
| Jun-79 | $ 386,522,000 | $ 129,144,000 | $ 37,289,000 | $ 34,972,000 | $ 587,927,000 |
| Jul-79 | $ 335,139,000 | $ 115,019,000 | $ 34,903,000 | $ 32,558,000 | $ 517,619,000 |
| Aug-79 | $ 350,478,000 | $ 109,873,000 | $ 32,367,000 | $ 32,458,000 | $ 525,176,000 |
| Sep-79 | $ 406,120,000 | $ 153,516,000 | $ 39,287,000 | $ 38,359,000 | $ 637,282,000 |
| Oct-79 | $ 385,789,000 | $ 146,476,000 | $ 32,805,000 | $ 33,741,000 | $ 598,811,000 |
| Nov-79 | $ 379,462,000 | $ 136,923,000 | $ 30,059,000 | $ 36,439,000 | $ 582,883,000 |
| Dec-79 | $ 466,989,000 | $ 169,630,000 | $ 40,157,000 | $ 39,931,000 | $ 716,707,000 |
| Jan-80 | $ 392,956,000 | $ 262,179,000 | $ 31,882,000 | $ 43,234,000 | $ 730,251,000 |
| Feb-80 | $ 411,300,000 | $ 272,646,000 | $ 33,274,000 | $ 44,030,000 | $ 761,250,000 |
| Mar-80 | $ 504,200,000 | $ 309,234,000 | $ 38,213,000 | $ 52,401,000 | $ 904,048,000 |
| Apr-80 | $ 451,518,000 | $ 284,251,000 | $ 31,867,000 | $ 49,323,000 | $ 816,959,000 |
| May-80 | $ 467,605,000 | $ 298,543,000 | $ 35,195,000 | $ 51,582,000 | $ 852,925,000 |
| Jun-80 | $ 507,533,000 | $ 315,488,000 | $ 40,166,000 | $ 53,415,000 | $ 916,602,000 |
| Jul-80 | $ 443,882,000 | $ 292,194,000 | $ 37,953,000 | $ 48,084,000 | $ 822,113,000 |
| Aug-80 | $ 434,960,000 | $ 275,809,000 | $ 34,765,000 | $ 47,819,000 | $ 793,353,000 |
| Sep-80 | $ 507,062,000 | $ 318,500,000 | $ 39,212,000 | $ 54,938,000 | $ 919,712,000 |
| Oct-80 | $ 437,564,000 | $ 295,256,000 | $ 35,221,000 | $ 50,904,000 | $ 818,945,000 |
| Nov-80 | $ 425,870,000 | $ 275,093,000 | $ 32,873,000 | $ 45,751,000 | $ 779,587,000 |
| Dec-80 | $ 517,595,000 | $ 303,696,000 | $ 39,250,000 | $ 48,240,000 | $ 908,781,000 |
| Jan-81 | $ 393,046,000 | $ 253,081,000 | $ 29,788,000 | $ 39,934,000 | $ 715,849,000 |
| Feb-81 | $ 410,033,000 | $ 266,800,000 | $ 32,348,000 | $ 43,840,000 | $ 753,021,000 |
| Mar-81 | $ 500,806,000 | $ 281,897,000 | $ 38,990,000 | $ 51,843,000 | $ 873,536,000 |
| Apr-81 | $ 419,016,000 | $ 244,517,000 | $ 33,132,000 | $ 45,892,000 | $ 742,557,000 |
| May-81 | $ 433,841,000 | $ 241,246,000 | $ 34,375,000 | $ 47,641,000 | $ 757,103,000 |
| Jun-81 | $ 506,599,000 | $ 242,903,000 | $ 39,886,000 | $ 50,909,000 | $ 840,297,000 |
| Jul-81 | $ 422,720,000 | $ 209,141,000 | $ 31,944,000 | $ 43,855,000 | $ 707,660,000 |
| Aug-81 | $ 422,625,000 | $ 187,007,000 | $ 31,260,000 | $ 45,078,000 | $ 685,970,000 |
| Sep-81 | $ 532,482,000 | $ 250,062,000 | $ 38,199,000 | $ 52,958,000 | $ 873,701,000 |
| Oct-81 | $ 440,180,000 | $ 219,306,000 | $ 33,044,000 | $ 44,071,000 | $ 736,601,000 |
| Nov-81 | $ 429,201,000 | $ 228,411,000 | $ 33,874,000 | $ 45,216,000 | $ 736,702,000 |
| Dec-81 | $ 512,340,000 | $ 246,667,000 | $ 40,996,000 | $ 52,231,000 | $ 852,234,000 |
| Jan-82 | $ 394,358,000 | $ 203,745,000 | $ 28,342,000 | $ 37,521,000 | $ 663,966,000 |
| Feb-82 | $ 408,299,000 | $ 230,007,000 | $ 30,738,000 | $ 42,308,000 | $ 711,352,000 |
| Mar-82 | $ 521,281,000 | $ 273,084,000 | $ 38,061,000 | $ 50,971,000 | $ 883,397,000 |
| Apr-82 | $ 467,258,000 | $ 236,183,000 | $ 32,055,000 | $ 45,544,000 | $ 781,040,000 |
| May-82 | $ 474,674,000 | $ 239,299,000 | $ 32,900,000 | $ 46,665,000 | $ 793,538,000 |
| Jun-82 | $ 523,760,000 | $ 248,336,000 | $ 38,126,000 | $ 54,405,000 | $ 864,627,000 |
| Jul-82 | $ 443,671,000 | $ 201,031,000 | $ 30,719,000 | $ 46,266,000 | $ 721,687,000 |
| Aug-82 | $ 450,028,000 | $ 197,921,000 | $ 29,752,000 | $ 45,188,000 | $ 722,889,000 |
| Sep-82 | $ 533,488,000 | $ 259,977,000 | $ 38,777,000 | $ 51,165,000 | $ 883,407,000 |
| Oct-82 | $ 417,937,000 | $ 219,585,000 | $ 32,473,000 | $ 47,317,000 | $ 717,312,000 |
| Nov-82 | $ 434,426,000 | $ 231,788,000 | $ 35,766,000 | $ 43,372,000 | $ 745,352,000 |
| Dec-82 | $ 536,395,000 | $ 256,210,000 | $ 40,514,000 | $ 53,234,000 | $ 886,353,000 |
Desired Output would look like:
Year Total Sales Y/Y Growth
1978 4,850,141,000
1979 6,611,597,000 36%
1980 10,024,526,000 52%
1981 9,275,231,000 -7%
1982 9,374,920,000 1%
TIA!
