# Power BI Exchange

View Only

## Last Date (Max Date) value from each month and then Average of it (YTD)

• #### 1.  Last Date (Max Date) value from each month and then Average of it (YTD)

Posted Jul 19, 2019 04:32 PM
Hi All,

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.

 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,
Abhi K
Power BI

------------------------------

• #### 2.  RE: Last Date (Max Date) value from each month and then Average of it (YTD) Best Answer

Gold Contributor
Posted Jul 22, 2019 06:30 AM
Hi @Abhi K,

To get the value on the last day of the month the measure would be something like:

```Value on Last Date =
CALCULATE(
AVERAGE( Data[VALUE] ),
FILTER( Data, Data[DATE] = MAX( Data[DATE] ) )
)​```
​If you put this into a matrix with Name as rows and Month (from your Date field) as columns then it will give you the numbers you want.
To then get the average of all months for a particular Name (or Location), you just need to average this measure over a list of months:

```Average of Values on Last Date =
AVERAGEX(
VALUES( Data[DATE].[Month] ),
[Value on Last Date]
)​```

Hope you can get that working with your data.

------------------------------
Simon Lamb
IT Consultant, Method Excel Ltd.
------------------------------