Forum Discussion

Faye1901's avatar
Faye1901
Icon for Helper I rankHelper 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: Project FY Forecast A Jun-19 10000 A Jul-19...
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
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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