Average of Each months last date (MAX date) value

Question

I am looking to calculate AVG OF EACH Month's last date (MAX date) value. Below is the sample data.

Can you please someone help me with the DAX caluclations. Much appreciated.

Please let me know if you need more information.

Location    DATE           NAME  VALUE  
P1         1/1/2019 0:00    ABC   12    
P1  1/2/2019 0:00   ABC 4   
P1  1/3/2019 0:00   ABC 50  
P1  1/4/2019 0:00   ABC 8   
P1  1/5/2019 0:00   ABC 35  ABC MaxDate value of JAN
P1  1/1/2019 0:00   DEF 20  
P1  1/2/2019 0:00   DEF 25  
P1  1/3/2019 0:00   DEF 66  
P1  1/4/2019 0:00   DEF 24  
P1  1/5/2019 0:00   DEF 50  DEF MaxDate value of JAN
P2  2/1/2018 0:00   ABC 28  
P2  2/2/2018 0:00   ABC 82  
P2  2/3/2018 0:00   ABC 67  
P2  2/4/2018 0:00   ABC 43  
P2  2/5/2018 0:00   ABC 66  ABC MaxDate value of FEB
P2  2/1/2018 0:00   DEF 28  
P2  2/2/2018 0:00   DEF 82  
P2  2/3/2018 0:00   DEF 67  
P2  2/4/2018 0:00   DEF 43  
P2  2/5/2018 0:00   DEF 34  DEF MaxDate value of FEB

what I need is Average of Max date values of each month as YTD ABC YTD = AVG(35+66)
DEF YTD = AVG(50+34)

Thanks,

what I need is Average of Max date values of each month as YTD ABC YTD = AVG(35+66)
DEF YTD = AVG(50+34)

OscarLar OscarLar · Accepted Answer · 2019-07-23T06:58:28

N.B: The Data table, or 'Data' refers to the data table you provided in the question.

Method 1:

Summarize the data table like this:

SummarizedTable = 
SUMMARIZE(
    ADDCOLUMNS(
        'Data';
        "Month"; MONTH([DATE]);
        "Year"; YEAR([DATE])
    );
    [Year];
    [Month];
    [NAME];
    "MaxDateValue" = 
    var MaxD = MAX([DATE])
    return
    CALCULATE(
        SUM('Data'[VALUE]);
        'Data'[DATE] = MaxD
    )
)

You end up with a new table looking like this:

enter image description here

On your dashboard you can build a visual table with [NAME] and [MaxDateValue] by drag and drop from your new table 'SummarizedTable'. Right click on [MaxDateValue] in the visualizing pane and select average instead of sum and you should end up with a visual table looking like this:

enter image description here

Method 2:

Instead of calculating a new table using summarize you can turn it into a measure. The benefit of this is that you retain the integrity of the original data and this makes it easier to slice.

AvgYtd = 
AVERAGEX(
    SUMMARIZE(
        ADDCOLUMNS(
            'Data';
            "Month"; MONTH([DATE]);
            "Year"; YEAR([DATE])
        );
        [Year];
        [Month];
        [NAME];
        "MaxDateValue" = 
        var MaxD = MAX([DATE])
        return
        CALCULATE(
            SUM('Data'[VALUE]);
            'Data'[DATE] = MaxD
        )
    );
    [MaxDateValue]
)

On your dashboard you can now create a new visual table by dragging [NAME] from the original Data table and then adding your new measure [AvgYtd] so that you have two tables showing the same thing:

enter image description here

However, you can now add a slicer, e.g. [Location] from the Data table and select P1:

enter image description here

You can see that the visual table created using the measure is affected by the slicer, while the summarized table is not.

Average of Each months last date (MAX date) value

Thanks,

1 Answers