Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 people who are 609 months old. Then I apply the entire dataset to a future state, without changing the data. So in 10 years time, there will STILL be 7 people who are 609 months old, but because it's 10 years from now, those people are Miliennials Instead of Gen X.
The graph matches the real data we are seeing for the past 3 years, so I'm happy with it. The problem is that I setup the data in Excel. I took the dataset, and copy pasted it over ten years worth of dates (just 1st of every month).
Now I want to build it dynamically in PowerBI.
I created a caculated table which summarizes the age in months and the count for the latest month's headcount data. Easy peasey. I created a second table with all the "forecast dates" using the MAX date on the headcount dataset as the starting point.
Here's where I'm stuck. How do I "merge" the two so that the counts by age in month are listed for every month in the forecast date range? Basically, the dataset need to repeat for every month in the forecasted date range.
I feel like it might only be possible in query, but I don't know how to summarize in power query language.
Solved! Go to Solution.
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |