Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CheanSereyvatan
Frequent Visitor

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

CheanSereyvatan_0-1711362828963.png

 

1 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.

vheqmsft_0-1711440989689.png

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



View solution in original post

4 REPLIES 4
CheanSereyvatan
Frequent Visitor

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

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.

vheqmsft_0-1711440989689.png

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



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
Community Support
Community Support

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:

vheqmsft_0-1711416565015.png

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

vheqmsft_2-1711416754841.png

 

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





Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.