Forum Discussion
Visualize workload per project/customer/product.
Hi,
I have a data set with the following columns
- Forecasted Start date of project
- Forecasted End date of project
- Actual Start date of project (blank if not started)
- Actual End date of project (blank if not ended)
- Number of personnel on project (split per category in separate columns)
In addition there are several parameters per row which is relevant for the end result when it comes to filtering/slicers etc...
| Project | Forecasted Start date | Forecasted End date | Actual Start date | Actual End date | Customer | Product | Number of personnel on project | Personnel category 1 | Personnel category 2 | Personnel category 3 |
| 1 | 07.03.2018 | 14.08.2018 | 01.03.2018 | A | 1 | 4 | 2 | 1 | 1 | |
| 2 | 01.01.2018 | 31.12.2018 | 01.01.2018 | A | 2 | 3 | 0 | 1 | 2 | |
| 3 | 30.03.2018 | 20.05.2019 | B | 3 | 3 | 3 | 0 | 0 | ||
| 4 | 19.08.2016 | 31.05.2017 | 20.08.2016 | 30.05.2017 | C | 3 | 6 | 2 | 2 | 2 |
First of all i have unpivoted the personnel categories columns so that there is only one column with number of personnel.
| Project | Forecasted Start date | Forecasted End date | Actual Start date | Actual End date | Customer | Product | Personnel category | number of personnel |
| 1 | 07.03.2018 | 14.08.2018 | 01.03.2018 | A | 1 | 1 | 2 | |
| 2 | 01.01.2018 | 31.12.2018 | 01.01.2018 | A | 2 | 1 | 0 | |
| 3 | 30.03.2018 | 20.05.2019 | B | 3 | 1 | 3 | ||
| 4 | 19.08.2016 | 31.05.2017 | 20.08.2016 | 30.05.2017 | C | 3 | 1 | 2 |
| 1 | 07.03.2018 | 14.08.2018 | 01.03.2018 | A | 1 | 2 | 1 | |
| 2 | 01.01.2018 | 31.12.2018 | 01.01.2018 | A | 2 | 2 | 1 | |
| 3 | 30.03.2018 | 20.05.2019 | B | 3 | 2 | 0 | ||
| 4 | 19.08.2016 | 31.05.2017 | 20.08.2016 | 30.05.2017 | C | 3 | 2 | 2 |
| 1 | 07.03.2018 | 14.08.2018 | 01.03.2018 | A | 1 | 3 | 1 | |
| 2 | 01.01.2018 | 31.12.2018 | 01.01.2018 | A | 2 | 3 | 2 | |
| 3 | 30.03.2018 | 20.05.2019 | B | 3 | 3 | 0 | ||
| 4 | 19.08.2016 | 31.05.2017 | 20.08.2016 | 30.05.2017 | C | 3 | 3 | 2 |
My end goal is to be able to visualize the data (both forecast and actulas) in i.e stacked charts per day/month/quarter etc..
I.e. a bar chart per month that shows the number of people (on the project, per product, per customer) muliplied with the number of days in the month.
An example would be; values for February 2018 a stacked bar chart per personnel category would show 28 days for category 2 and 56 days for category 3. This because project 2 started on January 1st and is forecasted to end at December 31st. Since there is no end date it would just bring in the full month into the calculation. If for some reason project 1 had an end date of February 27th the values in the bar chart would be 27 and 54 days for category 2 and 3 accordingly.
The bar chart for April stacked by personnel category would show
- 60 + 90 days for category 1. 60 from project 1 which have two personnel active in April, 90 days for project 3 which have 3 personnel active in April
- 30 + 30 days for category 2. 30 from project 1 and 30 from project 2
- 30 + 60 days fro category 3. 30 from project 1 and 60 from project 2.
My question is, am I on the correct path when it comes to the data modeling? I have though about unpivoting the date columns as well so that i only have two date columns; Start date and end date, and the Forecast/Actuals split as a column parameter. But i have not done this yet as im am 100% stuck when it comes to the time intelligence dax functions.
Any help at all would be highly appreciated.
Hi Anonymous,
Since you table store date in different columns as date range, I think time intelligence functions won't work for your scenario.
In my opinion, I'd like to suggest create new tables to expand these date range.
For example: Table formulas.
SpoilerForecast Detail Table = VAR _calendar = CALENDAR ( MIN ( Table2[Forecasted Start date] ), MAX ( Table2[Forecasted End date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table2, _calendar ), [Date] >= [Forecasted Start date] && [Date] <= [Forecasted End date] ), "Project", [Project], "Product",[Product], "Customer", [Customer], "Personnel category",[Personnel category], "Date", [Date] ) Actual Detail Table = VAR _calendar = CALENDAR ( MIN ( Table2[Actual Start date] ), TODAY () ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( FILTER ( ALL ( Table2 ), [Actual Start date] <> BLANK () ), _calendar ), [Date] >= [Actual Start date] && [Date] <= MAX ( [Actual End date], TODAY () ) ), "Project", [Project], "Product", [Product], "Customer", [Customer], "Personnel category", [Personnel category], "Date", [Date] )Then you can direct use above tables to analysis and operate with each day of date range.
Regards,
Xiaoxin Sheng
2 Replies
Hi Anonymous,
Since you table store date in different columns as date range, I think time intelligence functions won't work for your scenario.
In my opinion, I'd like to suggest create new tables to expand these date range.
For example: Table formulas.
SpoilerForecast Detail Table = VAR _calendar = CALENDAR ( MIN ( Table2[Forecasted Start date] ), MAX ( Table2[Forecasted End date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table2, _calendar ), [Date] >= [Forecasted Start date] && [Date] <= [Forecasted End date] ), "Project", [Project], "Product",[Product], "Customer", [Customer], "Personnel category",[Personnel category], "Date", [Date] ) Actual Detail Table = VAR _calendar = CALENDAR ( MIN ( Table2[Actual Start date] ), TODAY () ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( FILTER ( ALL ( Table2 ), [Actual Start date] <> BLANK () ), _calendar ), [Date] >= [Actual Start date] && [Date] <= MAX ( [Actual End date], TODAY () ) ), "Project", [Project], "Product", [Product], "Customer", [Customer], "Personnel category", [Personnel category], "Date", [Date] )Then you can direct use above tables to analysis and operate with each day of date range.
Regards,
Xiaoxin Sheng