Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
DRNEWTR
Frequent Visitor

last price change date measure

‎04-17-2024 01:54 AM

Hi how can i calculated last price change date

 

    How can i calculate in dax
RecidINVOICE_DATEProduct_NumberPurchise_PriceLast Purchise Change Date
150016.04.2024AS.124851009.04.2024
149915.04.2024AS.124851009.04.2024
149813.04.2024AS.124851009.04.2024
149712.04.2024AS.124851009.04.2024
149611.04.2024AS.124851009.04.2024
149509.04.2024AS.124851009.04.2024
149408.04.2024AS.124847027.03.2024
149306.04.2024AS.124847027.03.2024
149205.04.2024AS.124847027.03.2024
149104.04.2024AS.124847027.03.2024
149003.04.2024AS.124847027.03.2024
148902.04.2024AS.124847027.03.2024
148801.04.2024AS.124847027.03.2024
148730.03.2024AS.124847027.03.2024
148629.03.2024AS.124847027.03.2024
148528.03.2024AS.124847027.03.2024
148427.03.2024AS.124847027.03.2024
148326.03.2024AS.124845007.03.2024
148225.03.2024AS.124845007.03.2024
148123.03.2024AS.124845007.03.2024
148022.03.2024AS.124845007.03.2024
147921.03.2024AS.124845007.03.2024
147820.03.2024AS.124845007.03.2024
147719.03.2024AS.124845007.03.2024
147618.03.2024AS.124845007.03.2024
147516.03.2024AS.124845007.03.2024
147415.03.2024AS.124845007.03.2024
147314.03.2024AS.124845007.03.2024
147213.03.2024AS.124845007.03.2024
147112.03.2024AS.124845007.03.2024
147011.03.2024AS.124845007.03.2024
146909.03.2024AS.124845007.03.2024
146808.03.2024AS.124845007.03.2024
146707.03.2024AS.124845007.03.2024
Labels:
Message 1 of 3
850 Views
0
1 ACCEPTED SOLUTION
amustafa
Solution Sage
Solution Sage

‎04-17-2024 05:35 PM

Hi @DRNEWTR I suspect that you are trying to capture the invoice date when the purchase price is changed for each product. I sorted the data by Recid and created a calculated DAX column to get your desired results. See the attached .pbix file.

Last Purchase Change Date (DAX) = 
VAR CurrentRecid = 'Table'[Recid]
VAR CurrentPrice = 'Table'[Purchase_Price]
VAR CurrentProduct = 'Table'[Product_Number]
VAR PreviousPrice = CALCULATE(
    LASTNONBLANK('Table'[Purchase_Price], 1),
    FILTER(
        ALL('Table'),
        'Table'[Recid] < CurrentRecid
        && 'Table'[Product_Number] = CurrentProduct
    )
)
RETURN
IF(
    CurrentPrice <> PreviousPrice,
    'Table'[INVOICE_DATE],
    CALCULATE(
        MIN('Table'[INVOICE_DATE]),
        FILTER(
            ALL('Table'),
            'Table'[Recid] < CurrentRecid
            && 'Table'[Purchase_Price] = CurrentPrice
            && 'Table'[Product_Number] = CurrentProduct
        )
    )
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Last Purchase Change Date.pbix
Message 2 of 3
581 Views
2 REPLIES 2
ThxAlot
Super User
Super User

‎04-18-2024 02:57 AM

This is the scenario where WINDOW functions show the power.

 

ThxAlot_0-1713434056628.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Last Purchase Change Date.pbix
Message 3 of 3
555 Views
0
amustafa
Solution Sage
Solution Sage

‎04-17-2024 05:35 PM

Hi @DRNEWTR I suspect that you are trying to capture the invoice date when the purchase price is changed for each product. I sorted the data by Recid and created a calculated DAX column to get your desired results. See the attached .pbix file.

Last Purchase Change Date (DAX) = 
VAR CurrentRecid = 'Table'[Recid]
VAR CurrentPrice = 'Table'[Purchase_Price]
VAR CurrentProduct = 'Table'[Product_Number]
VAR PreviousPrice = CALCULATE(
    LASTNONBLANK('Table'[Purchase_Price], 1),
    FILTER(
        ALL('Table'),
        'Table'[Recid] < CurrentRecid
        && 'Table'[Product_Number] = CurrentProduct
    )
)
RETURN
IF(
    CurrentPrice <> PreviousPrice,
    'Table'[INVOICE_DATE],
    CALCULATE(
        MIN('Table'[INVOICE_DATE]),
        FILTER(
            ALL('Table'),
            'Table'[Recid] < CurrentRecid
            && 'Table'[Purchase_Price] = CurrentPrice
            && 'Table'[Product_Number] = CurrentProduct
        )
    )
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Last Purchase Change Date.pbix
Message 2 of 3
582 Views

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All