Extracting Date and Plan values matching latest Actual

[#1]

Hello all,

I have a project progress table with 3 columns: (1) Month-Year Date [Date], (2) Plan Completion [Plan] and (3) Actual Completion [Actual].  Monthly dates and Plan Completion % are filled for the entire plan of the project that goes upto Dec 2016.  But Actual completion get filled only at the end of each month.  month([Date]) can be used as a filter. 

So if no filter is selected, as an KPI, I need pull out the value of the latest [Actual] and show the matching [Date] and [Plan] values.  How do I do that?  And if a month is selected from the filter, I need the [Plan] and [Actual] for that Date.  How do I modify my expression to do that?

As an extension, the above assumes I have only 1 years data, so will work.  But what if I have 2 years data and I create a filter on Month and one on Year.  If I select only month, how can I get the data for the latest month for which Actual data is available?

Can someone help with the syntax for a text box for Date, Plan and Actual for both situations?

Thanks much,

[#2]

Do note that Actual is filled for the last month, so I'm already in the next month.  So today, actual is filled for Jan 2015.  There is no actual data for this month. 

[datanibbler]

Hi,

if you have more than one years'  data, you cannot allow the user to select only a month and no year, or you have to implement a standard_selection for that via a trigger.

If I have understood you correctly, the user will never select a date newer than the last month (e.g. right now, data in the   [Actual] would only be available for January, right?)

Well, just use a WHERE LEN([Actual]) >= 1 or something like that to only load the records where you have data in that field. If you have no external master_calendar, the user will not be able to select any dates for which there is no value in the field.

HTH

Best regards,

DataNibbler

[#4]

Hi DataNibbler, thanks for your response.

Yes, the user will never select a date newer than the last month.  All data is already loaded, because a corresponding graph shows the plan vs actual progression over time.

This is what I'm trying for the Plan field ... may need some help (overall and stage are separate filters):

=only({$<Level={"Overall"},Stage={"E-P-C"}, Actual={'$(=max(Actual))'}>} [Plan]) ... but not working;