cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keewei87
Frequent Visitor

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

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 VisitCustomer NameSales IndicatorCurrent Day Sales IndicatorCurrent Week Sales Indicator
11 OctCustomer AYesYesYes
11 OctCustomer ANoYesYes
10 OctCustomer BNoNoNo
1 OctCustomer BYesYesYes
1 OctCustomer CYesYesYes

 

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!!!!

 

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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]
    )
)

83.png

Best Regards,
Community Support Team _ Eason

 

View solution in original post

2 REPLIES 2
keewei87
Frequent Visitor

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

v-easonf-msft
Community Support
Community Support

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]
    )
)

83.png

Best Regards,
Community Support Team _ Eason

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.