Forum Discussion
Help please! Clustered column chart to count dates separately (three date columns per line item)
Hi new to Power BI - any help appreciated, looked for an existing solution on this forum but not found the same issue.
I have an excel table (imported) which tracks audits (rows), each with a 'Planned' (baseline) date, a 'Forecast' date and a 'Actual Complete' date. I have set up a clustered column chart which counts planned, forecast and actual dates. However, when I use 'Planned' field to provide date split on axis (by month), the table counts dates (cells with a date) based on Planned audits in the month - so it does not recognise the dates in forecast and actual columns - just that there is a date (data) in the cell.
I need it to recognise the dates and count these by month - not just count cells with data in it regardless of what that data (date) is?
I have set-up a DAX date table and linked this to my planned (active relationship), forecast (non-active) and actual (non-active), but it does the same thing?
'Sample' version of my file above if this helps!
Project AreaScopeLODRequirementAuditee / ReviewedFocal PointAuditor / ReviewerPlanned to Complete (Jul-19)Forecast to CompleteActual CompleteRemarksAudit Findings Tracker
| Sample Integrated Audit & Review Plan 2019 | Update at: | 29/08/2019 | |||||||||
| Project | Audit 1 | 2 | A | Project Team | Eng. Manager | Name 1 | 01/11/2019 | 01/11/2019 | |||
| Project | Audit 2 | 1 | B | HSE Team | HSSE Manager | Name 2 | 10/01/2019 | 10/01/2019 | |||
| Project | Review 1 | 1 | B | Project Team | Topsides EM | Name 3 | 20/05/2019 | 20/06/2019 | |||
| Topside | Audit 3 | 1 | A | Project Team | HSSE Manager | Name 2 | 15/10/2019 | 15/10/2019 | |||
| Topside | Review 2 | 1 | B | Project Team | HSSE Manager | Name 2 | 01/06/2019 | 15/03/2019 | |||
| Subsea | Audit 4 | 1 | A | Subsea Team | Eng. Manager | Name 1 | |||||
| Subsea | Audit 5 | 1 | A | Subsea Team | Marine Eng. Manager | Name 4 | 01/06/2019 | 01/07/2019 | |||
| Subsea | Review3 | 1 | B | Subsea Team | Marine Eng. Manager | Name 4 | 25/06/2019 | 25/07/2019 |
4 Replies
Hi Anonymous ，
Is what the screenshot shows below the result you want? If not, please give me more details then I can help you better. This is my PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey
This is type of chart I am looking for, however 'count' is not correct. If I look at the data (dates) in the table I should have:
Planned - 1 in January and May, 3 in June, 1 in October and November.
Actuals - 1 in January, March, June and 2 in July.
Forecast - 1 in October and November.
Is it possible for chart to count dates?
Hi Anonymous ,
Make sure your Planned, Actuals, and Forecast are in right format of date. And then, another Date table is not needed.
If your date format is not correct, change it:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
