Need help with Dax/Logic on Calculated columns for same category and same day

[keewei87]

Dear Powerbi Gods,

I have 2 databases. Customer_database and Visit_database.

They are linked by Customer Name on 1 to many relationship.

Visit_Database

Date of Visit	Customer Name	Sales Indicator	Current Day Sales Indicator	Current Week Sales Indicator
11 Oct	Customer A	Yes	Yes	Yes
11 Oct	Customer A	No	Yes	Yes
10 Oct	Customer B	No	No	No
1 Oct	Customer B	Yes	Yes	Yes
1 Oct	Customer C	Yes	Yes	Yes

Sales Indicator is a new column from a if else statement on the the products and denotes Yes if there is any sales done during this visit and No if no sales done during this visit.

I need to create 2 additional new columns

1) Current Day Sales indicator

- should denote Yes if there is any sales done for THIS CUSTOMER for THIS DAY regardless of no. of visits on the same day.

2) Current Week Sales indicator 

- should denote Yes if there is any sales done for THIS CUSTOMER for THIS WEEK regardless of no. of visits within the week.

My Dax is as appended but not working. 

Current Day Sales Indicator =
if('Visit_database'[Sales Indicator]="Yes", "Yes", "No")
FILTER('Visit_database', 'Visit_database[Customer] && 'Visit_database'[Date of Visit])

I got the logic but I do not know how to express it in DAX and not sure if I am doing the correct method.

Or maybe Current Day and Current Week indicator should be added columns in the Customer_Database instead.

My End Product which I am building towards to is

1) Bar chart showing Sales Done/Not Done for this customer over Days/Weeks

2) Overall Barchart how many Sales Done/Not Done over Days/Weeks

Have been stuck for months on this.

Grateful if you could help me. Thanks in advance!!!!

[keewei87]

Thanks alot!! This is exactly what I needed!!!😀😀

[v-easonf-msft · Accepted solution]

Hi, @keewei87 

Try  calculated columns as below:

Current Day Sales Indicator = 
CALCULATE (
    MAX ( Visit_database[Sales Indicator] ),
    ALLEXCEPT (
        Visit_database,
        Visit_database[Date of Visit],
        Visit_database[Customer Name]
    )
)

Weeknum = WEEKNUM(Visit_database[Date of Visit]) 

Current week Sales Indicator = 
CALCULATE (
    MAX ( Visit_database[Sales Indicator] ),
    ALLEXCEPT (
        Visit_database,
        Visit_database[Weeknum],
        Visit_database[Customer Name]
    )
)

Best Regards,
Community Support Team _ Eason