Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

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

Jump to Best Answer
  • 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.

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

    ------------------------------
    Conference-PBI_200x200


  • 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.
    ------------------------------

    Conference-PBI_200x200