Marking the last occurence in a table

[PBI-BOLA]

Hi,

I'm trying to have a cell to indicate whether a particular intry is the last occurence of an item in a table.

Below is an example. For each item I want to know if it is the last occurance of that item in the table. For example for Item E the last transaction is 1008

So the field I need help writing a formula for is "Last Occurance?"

Transaction	Item	Amount	Last occurrence?
1001	B	510	Y
1002	E	114	N
1003	A	464	N
1004	D	499	N
1005	C	874	N
1006	D	102	Y
1007	E	181	N
1008	E	316	Y
1009	C	976	Y
1010	A	308	Y

Thanks

[v-eqin-msft]

Hi @PBI-BOLA ,

According to my understanding, you want to set a "Y"/"N" flag for each item if it appears for the last time, right?

You could use the following formula to create a column :

Last Column =
IF (
    [Transaction]
        = CALCULATE ( MAX ( 'Table'[Transaction] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ),
    "Y",
    "N"
)

or measue like this:

Last Measure =
IF (
    MAX ( 'Table'[Transaction] )
        = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Item] = MAX ( 'Table'[Item] ) ),
            [Transaction]
        ),
    "Y",
    "N"
)

The final output is shown below:

You could take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI-BOLA]

Thanks for this. I think we may be on the right track. Initially I couldn't get your measure to work so I gave my Item field an index (Item Ind) as I figured it may be because the string is too complicated and applied the measure to this new field. Unfortunately it incorrectly recognises the last occurance for some lines. 

Below is some of my actual data which may help

I extracted the "MAXX" into a column named "Maxx Test" formula from the measure to see what was happening and as you can see for some lines it comes out blank

Any thoughts on what may need to be adjusted to fix this?

[Greg_Deckler]

@PBI-BOLA  - Perhaps a measure like:

Last occurrence = 
  VAR __Current = MAX('Table'[Transaction])
  VAR __Item = MAX('Table'[Item])
  VAR __Table = FILTER(ALL('Table'),[Item]=__Item)
  VAR __Last = MAXX(__Table,[Transaction])
RETURN
  IF(__Last = __Current,"Y","N")