Forum Discussion
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...
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
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.