Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Repeat Dataset Over date range on Calculated Table

So I'm building a report that forecasts headcount by generation (e.g. Baby boomers, etc.)   What I've done is take the total count of employees for each "age in # of months".  Like, there are 7 peo...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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

     

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi Anonymous

 

Is there any chance you can provide a simple example dataset to make it easier for us to suggest some approaches.

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hmm I was afraid of that.  I'll try my best.

Here's snippet of the data as extracted from source,

 

Date of BithRun Date
1/1/196730-Apr-17
1/2/196730-Apr-17
3/2/199130-Apr-17
3/3/199130-Apr-17
3/4/199130-Apr-17
11/23/197730-Apr-17
4/11/195930-Apr-17

 

Age in months is as simple as using DATEDIIF formula:

 

Date of BithRun DateAge in Months
1/1/196730-Apr-17603
1/2/196730-Apr-17603
3/2/199130-Apr-17313
3/3/199130-Apr-17313
3/4/199130-Apr-17313
11/23/197730-Apr-17473
4/11/195930-Apr-17696

 

Then I created a calculated table using the SUMMARIZE function on Age in Months, and added a column for count.

Age in MonthsCount
6032
3133
4731
6961

 

 

 

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 MonthsCountDate
6032May, 2017
3133May, 2017
4731May, 2017
6961May, 2017
6032June, 2017
3133June, 2017
4731June, 2017
6961June, 2017
6032July, 2017
3133July, 2017
4731July, 2017
6961July, 2017
6032Aug, 2017
3133Aug, 2017
4731Aug, 2017
6961Aug, 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 MonthsCountDateForecast Date of birthGeneration
60321-May-172/1/1967Gen X
31331-May-174/1/1991Millennial
47311-May-1712/1/1977Gen X
69611-May-175/1/1959Baby Boomer
60321-Jun-173/1/1967Gen X
31331-Jun-175/1/1991Millennial
47311-Jun-171/1/1978Gen X
69611-Jun-176/1/1959Baby Boomer
60321-Jul-174/1/1967Gen X
31331-Jul-176/1/1991Millennial
47311-Jul-172/1/1978Gen X
69611-Jul-177/1/1959Baby Boomer
60321-Aug-175/1/1967Gen X
31331-Aug-177/1/1991Millennial
47311-Aug-173/1/1978Gen X
69611-Aug-178/1/1959Baby 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...

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Crossjoin...  that's what I was missing.  I feel like a dolt!  Thank you.