Forum Discussion
Repeat Dataset Over date range on Calculated Table
Hi Anonymous
If you just need your basic pattern repeated over and over for every month, this approach might work. Create a calculated table and use the following DAX. You can adjust the start and enddate variables to suit.
New Table = Var StartDate = DATE(2017,5,1) Var EndDate = DATE(2017,8,1) var Months = SUMMARIZE( ADDCOLUMNS(CALENDAR( StartDate , EndDate ), "MonthID" , INT(FORMAT([Date],"YYYYMM")), "MonthName" , FORMAT([Date],"MMM, YYYY") ),[MonthID],[MonthName]) RETURN CROSSJOIN(Table1,Months)My Table1 looked like this.
Age in Months Count ---------------- ------- 603 2 313 3 473 1 696 1
Hi Anonymous
Is there any chance you can provide a simple example dataset to make it easier for us to suggest some approaches.
Hmm I was afraid of that. I'll try my best.
Here's snippet of the data as extracted from source,Date of Bith Run Date 1/1/1967 30-Apr-17 1/2/1967 30-Apr-17 3/2/1991 30-Apr-17 3/3/1991 30-Apr-17 3/4/1991 30-Apr-17 11/23/1977 30-Apr-17 4/11/1959 30-Apr-17 Age in months is as simple as using DATEDIIF formula:
Date of Bith Run Date Age in Months 1/1/1967 30-Apr-17 603 1/2/1967 30-Apr-17 603 3/2/1991 30-Apr-17 313 3/3/1991 30-Apr-17 313 3/4/1991 30-Apr-17 313 11/23/1977 30-Apr-17 473 4/11/1959 30-Apr-17 696 Then I created a calculated table using the SUMMARIZE function on Age in Months, and added a column for count.
Age in Months Count 603 2 313 3 473 1 696 1 The final table I need would look like this, where the date column contains future dated months. (I had to do this part manually in Excel by copying and pasting many many times)
Age in Months Count Date 603 2 May, 2017 313 3 May, 2017 473 1 May, 2017 696 1 May, 2017 603 2 June, 2017 313 3 June, 2017 473 1 June, 2017 696 1 June, 2017 603 2 July, 2017 313 3 July, 2017 473 1 July, 2017 696 1 July, 2017 603 2 Aug, 2017 313 3 Aug, 2017 473 1 Aug, 2017 696 1 Aug, 2017 When it's in this format, I can simply add a column for Date of Birth, adding the Age in Month to the Date column. I can then derive the Generation:
Age in Months Count Date Forecast Date of birth Generation 603 2 1-May-17 2/1/1967 Gen X 313 3 1-May-17 4/1/1991 Millennial 473 1 1-May-17 12/1/1977 Gen X 696 1 1-May-17 5/1/1959 Baby Boomer 603 2 1-Jun-17 3/1/1967 Gen X 313 3 1-Jun-17 5/1/1991 Millennial 473 1 1-Jun-17 1/1/1978 Gen X 696 1 1-Jun-17 6/1/1959 Baby Boomer 603 2 1-Jul-17 4/1/1967 Gen X 313 3 1-Jul-17 6/1/1991 Millennial 473 1 1-Jul-17 2/1/1978 Gen X 696 1 1-Jul-17 7/1/1959 Baby Boomer 603 2 1-Aug-17 5/1/1967 Gen X 313 3 1-Aug-17 7/1/1991 Millennial 473 1 1-Aug-17 3/1/1978 Gen X 696 1 1-Aug-17 8/1/1959 Baby Boomer So, nothing is happening here with such a small data set, but over time the counts for each generation change. Baby Boomers decline, Mellenials increase and start to taper off then decrease...
Hi Anonymous
If you just need your basic pattern repeated over and over for every month, this approach might work. Create a calculated table and use the following DAX. You can adjust the start and enddate variables to suit.
New Table = Var StartDate = DATE(2017,5,1) Var EndDate = DATE(2017,8,1) var Months = SUMMARIZE( ADDCOLUMNS(CALENDAR( StartDate , EndDate ), "MonthID" , INT(FORMAT([Date],"YYYYMM")), "MonthName" , FORMAT([Date],"MMM, YYYY") ),[MonthID],[MonthName]) RETURN CROSSJOIN(Table1,Months)My Table1 looked like this.
Age in Months Count ---------------- ------- 603 2 313 3 473 1 696 1
Crossjoin... that's what I was missing. I feel like a dolt! Thank you.