Forum Discussion
How to Get Max to work while grouping data
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.
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] ) ) )