Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Faye1901
Helper I
Helper I

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

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.