Forum Discussion

mrpowrbihelp's avatar
mrpowrbihelp
Frequent Visitor
8 years ago
Solved

last visit

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's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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 ] )
    )
)

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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 #