last visit

[mrpowrbihelp]

Hi,

I have a table that looks like this:

Account #     Visit Date     Items Carried                 Contact Name

124                1/8/16            Apples                            Mark

124                5/6/17            Blank                               Alan

124                8/4/17            Blank                              Jacob

125                7/1/17            Oranges                         Patrick

125                9/8/17            Oranges, Pears                Mike

I want to be able to see the last visit date, who they spoke to that day, and the last Items they carried.

Account #     Last Visit Date     Items Carried          Contact Name

124                8/4/17                 Apples                      Jacob

125                9/8/17                 Oranges, Pears          Mike

So even though the Last Vist for Account 124 was 8/4/17, the Items Carried column is not filled in so i need it to go back to the last time it was.

[Zubair_Muhammad · Accepted solution]

Hi @mrpowrbihelp

Use this Measure to get Last Item Carried

Last_Item_Carried =
LASTNONBLANK (
    CALCULATETABLE (
        ALL ( Table1[Items Carried] ),
        FILTER ( Table1, Table1[Account #] = SELECTEDVALUE ( Table1[Account #] ) )
    ),
    CALCULATE ( VALUES ( Table1[Items Carried] ) )
)

[Zubair_Muhammad · Accepted solution]

HI @mrpowrbihelp

These MEASURES to get the LAST VISIT DATE and the RELATED CONTACT NAME

Last_Visit_Date =
MAX ( Table1[Visit Date ] )

Last_Contact_Name =
CALCULATE (
    SELECTEDVALUE ( Table1[Contact Name] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Account #] = SELECTEDVALUE ( Table1[Account #] )
            && Table1[Visit Date ] = MAX ( Table1[Visit Date ] )
    )
)

[Greg_Deckler]

3 measures:

MaxDate = MAX(Items[Visit Date])

MaxContact = MAXX(FILTER(Items,Items[Visit Date]=[MaxDate]),[Contact Name])

MaxItems = MAXX(FILTER(Items,Items[Visit Date]=[MaxDate]),[Items Carried])

Put these into a table visualization with Account #

[Zubair_Muhammad · Accepted solution]

Hi @mrpowrbihelp

Use this Measure to get Last Item Carried

Last_Item_Carried =
LASTNONBLANK (
    CALCULATETABLE (
        ALL ( Table1[Items Carried] ),
        FILTER ( Table1, Table1[Account #] = SELECTEDVALUE ( Table1[Account #] ) )
    ),
    CALCULATE ( VALUES ( Table1[Items Carried] ) )
)

[Zubair_Muhammad · Accepted solution]

HI @mrpowrbihelp

These MEASURES to get the LAST VISIT DATE and the RELATED CONTACT NAME

Last_Visit_Date =
MAX ( Table1[Visit Date ] )

Last_Contact_Name =
CALCULATE (
    SELECTEDVALUE ( Table1[Contact Name] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Account #] = SELECTEDVALUE ( Table1[Account #] )
            && Table1[Visit Date ] = MAX ( Table1[Visit Date ] )
    )
)

[Zubair_Muhammad · Accepted solution]

Hi @mrpowrbihelp

See the File attached here as well