Tooltip "Cumulative size Operating" showing only till March. What could be the reason ?

[Kolumam]

Hi All,

I am trying to add a field called "Cumulative Size Operating" as a tool tip to this visual but it shows only till March. The Month in this visual is different from the month that cumulative size operating uses but since it has relationship it is showing but only till March. What could be the issue ?

Because it should show after March 2018 as well. Please help.

[Anonymous]

Can you please also post the Measure's code?

[Kolumam]

Hi

Please find the measure for Cumulative size operating.

Cumulative Size Operating = CALCULATE(SUM(Project[Operating Size]),FILTER(ALLEXCEPT(Project,Countries[Country],'Project
 Phases'[Phase]),AND(Project[COD Month] <= MAX(Dates[Last day of month]),OR(Project[Project Phase (with hold On Hold)]="Operating",Project[Project Phase (with hold On Hold)]="Completed"))))

[Anonymous]

To me it looks like the issue is with the Project[COD Month] <= MAX(Dates[Last day of month]) row.  This will be bound to the filter context of where you are in your graph.  In the case of what you displayed, you were hovered over in March.

Your response to me might be "But i've used an ALL statement".  While that will be true, you have used an all statement on your Project table, but i'd be guessing you are using a date table for this axis?

[Kolumam]

What I don't understand is the Month axes is coming from a different table called Forecast Month but this "Cumulative size operating" is coming from a Date Table. But they are linked each other and so the visual is working.

Can you help me with this by doing a remote session ?

[Anonymous]

I can't do a remote session no.  I help out on this forum when i have spare time, which is often short and sportatic.

[Kolumam]

Do you have any idea how I can fix this ? by replacing all except to all ?

[Anonymous]

Try including another ALL statement for the data containing the dates.

[Kolumam]

Cumulative Size Operating = CALCULATE(SUM(Project[Operating Size]),FILTER(ALLEXCEPT(Project,Countries[Country],'Project Phases'[Phase]),AND(Project[COD Month] <= MAX(Dates[Last day of month]),OR(Project[Project Phase (with hold On Hold)]="Operating",Project[Project Phase (with hold On Hold)]="Completed"))))

Where should I be putting the ALL here ?

[Anonymous]

My expectation is that your formula should look something like this:

Cumulative Size Operating = CALCULATE(
	SUM(Project[Operating Size]),	
	ALLEXCEPT(
		Project,
		Countries[Country],
		'Project Phases'[Phase]
	),
	ALL('Dates'),
	Project[COD Month] <= MAX(Dates[Last day of month]),
	OR(
		Project[Project Phase (with hold On Hold)] = "Operating",
		Project[Project Phase (with hold On Hold)] = "Completed"
	)
)

[Anonymous]

No worries, thats an easy fix by doing:

Cumulative Size Operating = VAR MaxDate = CALCULATE(
	MAX(Dates[Last day of month]),
	ALL('Dates')
)
RETURN
CALCULATE(
	SUM(Project[Operating Size]),	
	ALLEXCEPT(
		Project,
		Countries[Country],
		'Project Phases'[Phase]
	),	
	Project[COD Month] <= MaxDate,
	OR(
		Project[Project Phase (with hold On Hold)] = "Operating",
		Project[Project Phase (with hold On Hold)] = "Completed"
	)
)

[Kolumam]

Hi Ross,

Still it shows only till March. 😞

Thanks

Krishna

[Anonymous]

Does your Phase column contain anything that might be restricting your data to March?  Do your phases end then?

[Kolumam]

The Month field on X axes that shows for the revenue is different from the Dates[Month] but they are related but the information is not captured correctly. Could you please advice ?

[Anonymous]

That will be the cause of the issue, as we have been addressing the ALL statements on a completely different date column.

[Kolumam]

I tried changing the phase to In Construction but still doesn't work

[Kolumam]

It gives me an error saying that MAX cannot be used as true/false that invloves table filter expression.