Finding closest dates that are filtered by another column

[RossBateman96]

Hello

I've added an example of the table below:

Im trying to populate another table with meter readings closest to to the last day of the month and split by meter. I managed to do this on excel (without filtering by meter) by using the index and min functions. 

Does anyone know of a formula that is capable of doing this? The end aim is to get the difference in every meter reading from month to month.

Thanks in advance, Ross

[v-jingzhang · Accepted solution]

Hi @RossBateman96 

Assume the first table is called Table1 and the second table is Table2, you can add two calculated columns to the second table with below DAX. 

A-MAIN =
MAXX (
    TOPN (
        1,
        FILTER ( Table1, Table1[Meter] = "A-MAIN" ),
        ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
    ),
    Table1[Date]
)

B-MAIN = 
MAXX (
    TOPN (
        1,
        FILTER ( Table1, Table1[Meter] = "B-MAIN" ),
        ABS ( DATEDIFF ( Table2[MonthEnd], Table1[Date], DAY ) ), ASC
    ),
    Table1[Date]
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.