Forum Discussion
How to Get Max to work while grouping data
Thanks Ashish,
You have introduced me to a new function I now need to learn being SUMMARIZE.
The solution you have proposed works for July but in the pbix you have saved doesnt work when the filter is updated to prior months. In appication, I am trying to show what a forecast was at any given period not just at the most recent. There are also circumstances where multiple forecasts are loaded and I only want the most recent as well as times where no forecast is loaded in the month and I want to gave the most recent prior to the filter period. I have attached some better data to show this and the expected outcomes.
At the moment my calendar is not related to my data table as I only need it for the filter measure not necessarily to filter the data table.
| Project Number | Asset Group | Forecast Date | Amount |
| Project 1 | Asset 1 | 23-Jul-18 | 11 |
| Project 1 | Asset 1 | 23-Jun-18 | 29 |
| Project 1 | Asset 1 | 24-May-18 | 12 |
| Project 2 | Asset 1 | 25-Jul-18 | 18 |
| Project 2 | Asset 1 | 27-May-18 | 13 |
| Project 2 | Asset 1 | 22-May-18 | 27 |
| Project 3 | Asset 2 | 26-Jul-18 | 20 |
| Project 3 | Asset 2 | 26-Jun-18 | 21 |
| Project 3 | Asset 2 | 27-May-18 | 28 |
| Asset 1 Output | |
| Jul-18 | 11+18 = 29 |
| Jun-18 | 29+13 = 42 |
| May-18 | 12+13 = 25 |
Hi,
I do not understand your requirement. For May, why should the output be 12+13? For June, why should it be 29+13?
In June the most recent forecast prior to 30-Jun for Project 1 is 29 (date 23-jun), for Project 2 is 13 (date 27-May).
For May Project 1 is 12 (24-May) and project 2 is 13 (27-May).
This is the most recent prior to the filter date. It doesnt matter what period it relates to it is just trying to find what was the most recent forecast prior to the date.
The forecast period in this example is the date the forecast was uploaded not the period the amount relates to.
You may try the measure below.
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Project Number], Table1[Asset Group] ), CALCULATE ( SUMX ( TOPN ( 1, Table1, Table1[Forecast Date], DESC ), Table1[Amount] ) ) )Thanks for all the attempts.
Ashish, your new measure works only because there are two projects in the filter in asset 1. If you filter on Asset 2 you will notice it will take the top 2 values, where it needs to be dynamic according to the dataset (the top 1 of each unique project based on the filter).
Chuncz yours works except for the ones where there is no data in the current month. What I need it to do is look for the top 1 before the end of the month for each unique project. Basically Max prior to period chosen if there is no forecast uploaded in the current period.
What yours is doing
Project 1 Project 2 Total Jul 11 18 29 Jun 29 missing 29 May 12 13 25 Ashish = SUMX ( CALCULATETABLE ( TOPN ( 2, Data, Data[Forecast Date], DESC ), DATESBETWEEN ( 'Calendar'[Date], MINX ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ) ), [Total] ) -------------------------------------------------------------------------------- Chuncz = SUMX ( SUMMARIZE ( Data, Data[Project Number], Data[Asset Group] ), CALCULATE ( SUMX ( TOPN ( 1, Data, Data[Forecast Date], DESC ), Data[Amount] ) ) )
