Filter on the dimClient based on factSales

[Anonymous]

Hello DAX experts,

I have two tables, a customer table and a sales table, linked by the client ID, in a one-to-many relationship.

Ex: Clients

Client ID	Name
1	aaa
2	bbb
3	ccc

Ex: Sales 

Sales ID	Client ID	Category	Date
1	1	A	25/11/2021
2	1	A	24/11/2021
3	2	A	24/11/2021
4	2	A	20/11/2021

Ex: Date of Today: 25/11/2021

My goal is to create a measure based on "Clients" table, filter by sales category with the date of last purchase, like:
Last Purchase: Today     -      nº clients: 1
Last Purchase: Yesterday   -  nº clients: 1

One measure so far:

Purchase Yesterday: =
CALCULATE (
    DISTINCTCOUNT ( Clients[ID] ),
    FILTER (
        Sales,
        Sales[Category] = "A"
        && IF (
                DATEDIFF (  Calculate(
                    LASTDATE ( Sales[Date] ),
                    REMOVEFILTERS(Sales[Date])), today(), DAY ) > 1
                && DATEDIFF (  Calculate(
                    LASTDATE ( Sales[Date] ),
                    REMOVEFILTERS(Sales[Date])), today(), DAY ) <= 2,
                TRUE(),
                FALSE()
            )
    )
)

But this measure is giving higher values than supposed.
Does anyone know a workaround for this problem?

Thank you very much,

Simao

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Here's my solution. 

1. Create a meaure to get the ranking. Calculate the ranking based on the date, and then the first one is the last purchase date.

Rank =
RANKX (
    FILTER ( ALL ( 'Sales' ), [Client ID] = MAX ( 'Sales'[Client ID] ) ),
    CALCULATE ( MAX ( 'Sales'[Date] ) ),
    ,
    DESC,
    DENSE
)

If there are other categories, you can modify it to

Rank =
RANKX (
    FILTER (
        ALL ( 'Sales' ),
        [Client ID] = MAX ( 'Sales'[Client ID] )
            && [Category] = MAX ( 'Sales'[Category] )
    ),
    CALCULATE ( MAX ( 'Sales'[Date] ) ),
    ,
    DESC,
    DENSE
)

2.Create the following two measures to get the results.

Purchase Yesterday: = VAR _TODAY=DATE(2021,11,25)
RETURN CALCULATE(DISTINCTCOUNT(Sales[Client ID]),FILTER('Sales',[Category]="A"&&[Date]=_TODAY-1&&[Rank]=1))Purchase Today: = 
VAR _TODAY =
    DATE ( 2021, 11, 25 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[Client ID] ),
        FILTER ( 'Sales', [Category] = "A" && [Date] = _TODAY && [Rank] = 1 )
    )

Purchase Yesterday: = 
VAR _TODAY =
    DATE ( 2021, 11, 25 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[Client ID] ),
        FILTER ( 'Sales', [Category] = "A" && [Date] = _TODAY - 1 && [Rank] = 1 )
    )

Best Regards,

Stephen Tao

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

[PaulDBrown]

Try:

Purchase Yesterday =
VAR Yesterday =
    TODAY () - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[Client ID] ),
        FILTER (
            ALLEXCEPT ( Sales, Sales[Category], Sales[Client ID] ),
            Sales[Date] = Yesterday
        )
    )

[Anonymous]

Thanks for your answer,

But this does not solve my problem because if a client purchases today and yesterday will count in the two categories. I need to know the frequency of last purchase date