Help please! Clustered column chart to count dates separately (three date columns per line item)

[Bennesim]

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

[Icey]

Hi @Bennesim ，

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.

[Bennesim]

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?

[Icey]

Hi @Bennesim ,

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.

[Bennesim]

Hi @Icey 

I have changed date type but still the same result.  What I get is the picture on the left - what I want is the picture on the right. 

I can only produce picture on the right by creating a new data table in excel which counts dates by month.  Problem with this is that it is not linked to the other data in the table if I click on it in Power BI.

I think problem is that I am using "Planned to Complete" field on the axis to give me the date range so data is only counted on basis of this column rather than independently.

Kind regards,

---

@Icey wrote:

Hi @Bennesim ,

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,

Iceyy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

Simon