Forum Discussion

Faye1901's avatar
Faye1901
Helper I
7 years ago
Solved

Data Splitting in table when data is not summarized?

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:

ProjectFYForecast
AJun-1910000
AJul-19120000
AAug-19110000
ASep-1910000

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

 

Need Help
Tips and Tricks
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    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

1 Reply

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    7 years ago

    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