Dear Sirs,

[CheanSereyvatan]

I try to calculate the last sale record of each customer and product with the date rank. let say now we stand on 25/Mar/2024. I want to calculate the last sale record of each customer and product during 5 days back. 

Please see the below sample. Thanks

[CheanSereyvatan · Accepted solution]

Dear Albert He,
It nearly to the point. How to add date rank in to the calculation? I dont want to calculate the whole transaction as we have 5 years data with 6 millions record already. Here I want it to calculate back 15 day till now. For example, now we are 26-Mar-2024. I want to calculate the last sale record from 11-Mar-2024 to 26-Mar-2024. 
Best Regards,

Vatanak

[CheanSereyvatan · Accepted solution]

Dear Albert He,
It nearly to the point. How to add date rank in to the calculation? I dont want to calculate the whole transaction as we have 5 years data with 6 millions record already. Here I want it to calculate back 15 day till now. For example, now we are 26-Mar-2024. I want to calculate the last sale record from 11-Mar-2024 to 26-Mar-2024. 
Best Regards,

Vatanak

[v-heq-msft · Accepted solution]

Hi @CheanSereyvatan ,
Depending on your requirements, you can try the following methods
1.Create a new date table based on Table[Date].

Date = VALUES('Table'[Date])

2.Create a slicer accroding to Date[Date]
3.Modified measure

Last Sale Record = 
VAR _RES =
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALLEXCEPT('Table', 'Table'[Customer ID], 'Table'[Product Name]),
        DATEDIFF('Table'[Date],SELECTEDVALUE('Date'[Date]),DAY) >= 1
    )
)
RETURN
IF(
    SELECTEDVALUE('Table'[Date]) = _RES,
    1,
    0
)

Final output
The table on the right is the initial table after filtering by date range, i.e. before grouping by id and product.

If you want to modify the filtering time range, you can modify the parameter after datediff.

Best regards,

Albert He

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

[CheanSereyvatan]

Dear Albert He,

Thanks for your idea. It's realy greate. On top of this, It only work when we put all data in like date, customer ID, Product name,...... but it doesn't work when I want to show only the qty of last sale date. Can you share if there is another way to calculate this? Thanks

[v-heq-msft]

Hi @CheanSereyvatan ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a measure

Last Sale Record = 
VAR _RES =
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT('Table', 'Table'[Customer ID], 'Table'[Product Name])
)
RETURN
IF(
    SELECTEDVALUE('Table'[Date]) = _RES,
    1,
    0
)

Apply the measure to the filter on this vixual

Best regards,

Albert He

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