Create a new column based on one value in a group

[V_PO]

Hi all,

I am trying to create a new column in a table, that looks into each "Customer" and enters the same value for all rows of this customer, based on a condition for the "Date".

Simple example explainig what I am trying to do:

Original Table:

Customer		Date		Order Amount
Customer A		Jan 20		10
Customer A		Feb 20		5
Customer A		Mar 20		10
Customer B		Feb 20		20
Customer B		Mar 20		5
Customer B		May 20		20
Customer C		May 20		10

Expected Result:
I want to add a column, that identifies all "Active Customers", based on whether they have made an order in the current month (May 2020) or not. The result I want to achieve looks like this:

Customer		Date		Order Amount		Active Customer
Customer A		Jan 20		10		No
Customer A		Feb 20		5		No
Customer A		Mar 20		10		No
Customer B		Feb 20		20		Yes
Customer B		Mar 20		5		Yes
Customer B		May 20		20		Yes
Customer C		May 20		10		Yes

All rows for Customer B and Customer C are marked as Active Customer = "Yes", because at least one of the rows for this client had Date = Current Month (in this case "May 20").

Any help getting closer to this result is highly appreciated!

Thanks,

Vlad

[v-gizhi-msft]

Hi,

Please try a simpler logic column as below:

Active Customer = 
IF (
    CALCULATE (
        COUNT ( 'Table'[Customer] ),
        FILTER (
            'Table',
            'Table'[Customer] = EARLIER ( 'Table'[Customer] )
                && MONTH ( 'Table'[Date] ) = MONTH ( TODAY () )
        )
    ) >= 1,
    "Yes",
    "No"
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[parry2k · Accepted solution]

@V_PO 

try following

Flag =
VAR __latestDate = CALCULATE ( MAX ( Table[Date] ), ALLEXCEPT ( Table, Table[Customer] ) )
VAR __latestEOM = EOMONTH ( __latestDate, 0 )
VAR __todayEOM = EOMONTH ( TODAY(), 0 )
RETURN
IF ( __latestEOM = __todayEOM, "YeS", "No" )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@V_PO 

I think its actually probably simpler to add a field to your customer table with the most recent order date, and then relate those two tables. That way you can cross filter all relevant customer facts and dims on the attribute.

By the way, @parry2k's solution should work but I find the answer a bit too specific.

Feel free to reply here if you need more help with what i mean.