DAX formula - in a new column

[Anonymous]

Hi, 

I have the below table for which I want to retrieve this Output column, using a DAX formula. The logic should be the following: If for the same month & item if I have value 1 for more than one entity , retrive 1 only for the last entity based on Date column and for the first one put 0. For the rest, including the same entity, leave the Value column. Basically, in my exemple, the only change should be done for the first item only because for the Item A, Month May I have the 1 value for 2 different entities and the one that should keep the 1 value should be the last one, meaning MNO, for the other (XYZ) we should have 0 in May 2020. 

Can someone provide me an urgent answer, please? 

Thank yoooou!

Month	Date	Item	Entity	Value	Output
May-20	5/1/2020	A	XYZ	1	0
May-20	5/1/2020	B	XYZ	1	1
May-20	5/3/2020	A	MNO	1	1
May-20	5/3/2020	A	XYZ	0	0
May-20	5/4/2020	C	XYZ	1	1
Jun-20	6/4/2020	A	XYZ	1	1

[Greg_Deckler · Accepted solution]

@Anonymous - I did it like below. PBIX is attached. Let me know if it works for your larger dataset.

Output = 
    VAR __Count = 
        COUNTROWS(
            SUMMARIZE(
                FILTER('Table',[Month] = EARLIER('Table'[Month]) && [Item] = EARLIER('Table'[Item]) && [Value] = 1),
                [Entity]
            )
        )
RETURN
    IF(__Count <= 1 || [Value] = 0,[Value],
            VAR __LastDate = MAXX(FILTER('Table',[Month] = EARLIER('Table'[Month]) && [Item] = EARLIER('Table'[Item]) && [Value] = 1),[Date])
        RETURN
            IF([Date] = __LastDate,1,0)
    )