Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate average last value in Power BI

Hello all ! I am trying to calculate the average of last dates in a table. Assume that I have the following table :
 
CustomerCodeDateValue
10003.06.202270,0
10114.06.202297,8
10302.06.202289,0
10910.06.202280,7
10824.05.202272,8
10624.05.202283,2
10416.05.202296,2
11420.05.202265,9
11401.06.202287,3
12019.05.202297,1
11913.06.202261,7

 

I want to have 87.3 for customer 114. And then calculate the average of all values. In SQL I would use Rank () over (partition by customercode order by date desc) and then choosing rank =1 and an average on top of that. But I don't know how to do this in DAX.

Please note that in the final report the end user can put year-quarter or year-month instead of daily date but he should be able to see the average of last value in the quarter or the month.

I have tried multiple ways but not succeeded. Any help would be much appreciated.

Need Help
Show and Tell

5 Replies

      • lucadelicio's avatar
        lucadelicio
        Icon for Impactful Individual rankImpactful Individual
        3 years ago

        Your request are not clear.
        In the pbix there yet the solution of the latest value for the customer code.
        This measure calculate the avg for customer code:

        Measure =
        AVERAGEX(
            VALUES('Table'[CustomerCode])
            ,AVERAGE('Table'[Value])
        )
        If you are more specific with your request i will help you.
        Thank you