Forum Discussion

V_PO's avatar
V_PO
Frequent Visitor
6 years ago
Solved

Create a new column based on one value in a group

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
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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!

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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