How to Get Max to work while grouping data

[TheGreatYOLO]

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.

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	25-Jun-18	13
Project 2	Asset 1	26-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

Dax Formulas I am using

Max Project Date = CALCULATE(MAX(Forecast Date),
	FILTER(ALL(Forecast Date), Forecast Date < MAX (Calendar Filter Date)))

Forecast Amount = CALCULATE(SUM(Amount),
	FILTER(ALL(Forecast Date),Forecast Date = [Max Project Date]))

If I filter Project 1 for Jul = 11 (the correct outcome)

When I filter Asset 1 for Jul = 18 (Max of Project 2). What I want the outcome to be is 29 (11+18) being the max of each project added together.

Does anyone know how to write this so it works for both a project and asset filter? This needs to work based on a Max function as there can be duplicate forecasts in a month and only the most recent is needed per project and months where there is no forecast and it would grab the most recent prior to the forecast period.

I had a go at a caluclated column but I couldnt get the calendar filter to work as filters do not come accross when doing calculated columns.

Thanks in advance

[Ashish_Mathur]

Hi,

This measure works.  You may download my PBI file from here.

=SUMX(FILTER(SUMMARIZE(Data,'Calendar'[Year],'Calendar'[Month],"EFGH",CALCULATE(FORMAT(MAX(Data[Forecast Date]),"mmyy"),ALL('Calendar')),"IJKL",FORMAT(MAX('Calendar'[Date]),"mmyy"),"ABCD",SUM(Data[Amount])),[IJKL]=[EFGH]),[ABCD])

Hope this helps.

[TheGreatYOLO]

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

[Ashish_Mathur]

Hi,

I do not understand your requirement.  For May, why should the output be 12+13?  For June, why should it be 29+13?

[TheGreatYOLO]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TheGreatYOLO]

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

[v-chuncz-msft]

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