Count Value Once in Column

[Anonymous]

Hello,

In the New Sale column I want to identify the first instance of the UnitRefNo, this can be based on the date or first receipt number.  

ReceiptNumber	ReceiptAmount	PropertyID	UnitRefNo	ReceiptDate	New Sale
10257	₦ 500,000.00	3275	TR6251	15/11/2018 00:00	New Sale
10258	₦ 500,000.00	3275	TR6251	15/11/2018 00:00	New Sale
10259	₦ 500,000.00	3275	TR6251	16/11/2018 00:00
10260	₦ 500,000.00	3275	TR6251	16/11/2018 00:00

I saw the following code provided by @Zubair_Muhammad however is it returning duplicate values for the 'New Sale' whereas it should just be the first instance.

New Sale =

VAR IDCount =

CALCULATE ( COUNTROWS ( GetReceiptsData ), ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] ) ) = 1

VAR First_Date =

GetReceiptsData[ReceiptDate]

= CALCULATE ( FIRSTDATE( GetReceiptsData[ReceiptDate] ), ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] ) )

RETURN

IF ( OR ( IDCount, First_Date ), "New Sale" )

Any help would be appreciated. Thanks in advance. 

[v-yuta-msft · Accepted solution]

@Anonymous ,

You may add a new value to discriminate if the ReceiptNumber is the first instance per ReceiptDate like below:

New Sale =
VAR IDCount =
    CALCULATE (
        COUNTROWS ( GetReceiptsData ),
        ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
    ) = 1
VAR First_Date =
    GetReceiptsData[ReceiptDate]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptDate] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
VAR First_Instance =
    GetReceiptsData[ReceiptNumber]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptNumber] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
RETURN
    IF ( AND ( OR ( IDCount, First_Date ), First_Instance ), "New Sale" )

Community Support Team _ Jimmy Tao

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