Forum Discussion

tvogel8570's avatar
tvogel8570
Regular Visitor
6 years ago
Solved

Filter for newest date by client

I am trying to create pivots that summarize the most recent invoice for each client by a variety of measures (region, category, sales person, etc).  If I leave the client in the pivot, the correct in...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    tvogel8570 OK, I think this makes things more clear. Couple quick questions. 

    1. Do you have the Row column available in your data?

    2. Is the logic the following - summarize (group) the data by Category and Client taking the latest date and then sum the Amt that corresponds to that grouping at those latest date values?

     

    If that logic is correct, then:

    Measure =
  VAR __Category = MAX([Category])
  VAR __Table =
    ADDCOLUMNS(
      SUMMARIZE(
        'Table',
        [Client],
        "__Date",MAX([Date])
      ),
      "__Amt",MAXX(FILTER(ALL('Table'),[Category]=__Category && [Client]=EARLIER([Client]) && [Date]=EARLIER([__Date])),[Amt])
    )
RETURN
  SUMX(__Table,[__Amt])

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

tvogel8570 , use these new measures

 

Try this as new measure

calculate( sum(Table[Amount]),filter(Table,Table[Inv Date]<=max(Table[Inv Date])),allexcept(Table,Table[Client]))

 

or these

new Inv Num =lastnonblankvalue(Table[Inv Date],Table[Inv Num])
new Inv Amount =lastnonblankvalue(Table[Inv Date],Table[Amount])
max(Table[Inv Date])