Forum Discussion

CheanSereyvatan's avatar
CheanSereyvatan
Frequent Visitor
2 years ago
Solved

Dear Sirs,

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

 

Need Help
  • CheanSereyvatan's avatar
    CheanSereyvatan
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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



4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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





  • CheanSereyvatan's avatar
    CheanSereyvatan
    Frequent Visitor
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        CheanSereyvatan
        Frequent Visitor
        2 years ago

        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