Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |