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

Last unique date per product and customer as a filter

I am trying to make a report based on my clients dustribution in each store that they sell products in. So its basically a table that when you select a specific item, you will see all the stores that had it in stock during the last check the seller did, and all the stores that didnt have it.  So something like this, select product on the left and then get a list with all stores that have the product "JA", and all stores that dont have it "NEJ".


Skärmavbild 2020-06-11 kl. 15.07.18.png

My issue is that the datafile contains all the visits that the seller have done in each store. So i want to be able to only use the data from the last visit the seller did, not the old ones. By filtering on latest date i get most of them, but not all. Since not all stores get checked on the same days, i want to get each unique customers and products last date. So in this example:
Customer 35
Product: 59
Product exists: 0 (0 means NO) at the last date it was checked the 20/11-2019.

But i also want to get the Yes from the same customer but another product
Customer:35
Product: 63
Product Exists (1 = Yes) on the 13/3.2019.

The this list goes on for ever with lots of customers and products (think its about 2.000.000 rows). So i would like to hear if someone can help me out on how to get all the unique "Product Exists = 0 or 1" based on each products last checked date (per customer).Skärmavbild 2020-06-11 kl. 15.09.37.png

1 REPLY 1
v-alq-msft
Community Support
Community Support

Hi, @Marcusdahlberg 

 

Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

d1.png

 

You may create a measure as below.

ProductExists measure = 
var _maxdate = 
CALCULATE(
    MAX('Table'[AppointmentStartDateTime]),
    FILTER(
        ALL('Table'),
        'Table'[CustomerID]=SELECTEDVALUE('Table'[CustomerID])&&
        'Table'[ProductID]=SELECTEDVALUE('Table'[ProductID])
    )
)
return
CALCULATE(
    SUM('Table'[ProduceExists]),
    FILTER(
        ALL('Table'),
        'Table'[CustomerID]=SELECTEDVALUE('Table'[CustomerID])&&
        'Table'[ProductID]=SELECTEDVALUE('Table'[ProductID])&&
        'Table'[AppointmentStartDateTime]=_maxdate
    )
)

 

Result:

d2.png

 

Best Regards

Allan

 

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

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.