How to find out last year item last post date

[Anonymous]

I need calculate last year meter reading data, for example, Item No: EQ10271, 2013 begin of year is 88, end of year is: 210, so this year, YTD Meter is : 210-88. but for 2014, begin of year is 210, end of year is 1220, I need 1220-210. now, I cannot find out "210" for 2014.  how to get this data, thank you very much

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use following calculated column formula if it works for your scenario.(it will lookup previous data if first date same as last date)

Diff =
VAR list =
    CALCULATETABLE (
        VALUES ( Table[Count.Posting_Date] ),
        FILTER (
            ALL ( Table ),
            [Service_Item_No] = EARLIER ( Table[Service_Item_No] )
                && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] )
        )
    )
VAR first =
    MINX ( list, [Count.Posting_Date] )
VAR last =
    MAXX ( list, [Count.Posting_Date] )
VAR previous =
    IF (
        first = last,
        MAXX (
            FILTER (
                ALL ( Table ),
                [Service_Item_No] = EARLIER ( Table[Service_Item_No] )
                    && [Posting_Date-Copy] = EARLIER ( Table[Posting_Date-Copy] )
                    && [Count.Posting_Date] < last
            ),
            [Count.Posting_Date]
        ),
        first
    )
RETURN
    LOOKUPVALUE (
        Table[Count.Reading],
        Table[Service_Item_No], [Service_Item_No],
        Table[Count.Posting_Date], last
    )
        - LOOKUPVALUE (
            Table[Count.Reading],
            Table[Service_Item_No], [Service_Item_No],
            Table[Count.Posting_Date], previous
        )

Regards,

Xiaoxin Sheng