Data Splitting in table when data is not summarized?

[Faye1901]

Hi All,

I'm trying to pull the latest set of values from a Sharepoint list without summarising the data. My raw data looks a bit like this:

Project	FY	Forecast
A	Jun-19	10000
A	Jul-19	120000
A	Aug-19	110000
A	Sep-19	10000

When I put these values in a matrix where I want to see the forecast for the latest month (so a single value for each project) it summarizes the forecast column to give the total for the whole column. When I use the Dont Summarize feature under Values, it splits the data to show multiple entries, even when I'm using some DAX to attempt to show only the last date (LastFYdate = calculate(LASTDATE('PortfolioActuals'[Financial Year]))) - see below.

Any ideas on how I can display just the latest forecast value, without showing multiple entries for a single project?

Thanks
Faye

[Phil_Seamark · Accepted solution]

Hi @Faye1901

I think this calculated measure gets close.  I have attached a PBIX file with a working example.

Measure = 
VAR MyProject = MAX('Table1'[Project])
VAR myFilteredTable = FILTER('Table1','Table1'[Project] = MyProject)
VAR LatestDate = MAXX(myFilteredTable,[FY])
VAR LatestForecast = MAXX(FILTER(myFilteredTable,[FY] = LatestDate),'Table1'[Forecast])
RETURN LatestForecast