Forum Discussion

TheGreatYOLO's avatar
TheGreatYOLO
Frequent Visitor
7 years ago

How to Get Max to work while grouping data

I have been struggling with a problem a couple times and wanted to know if there was a solution someone has for this problem.   The issue is how do I get MAX to work when grouping data.    Pro...
Need Help
TheGreatYOLO's avatar
TheGreatYOLO
Frequent Visitor
7 years ago

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.

v-chuncz-msft's avatar
v-chuncz-msft
Community Support
7 years ago

TheGreatYOLO,

 

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] )
    )
)