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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frankhofmans
Helper IV
Helper IV

Track change in table

hi PBI experts,

 

i have the following question:

 

I have a contract database (with > 50 colums en > 100.000 rows). Some contracts have changes in within a year:

 

Contract yearDate startDate endContract IDSelling pricePayment methodPayment term
20221-1-20221-2-2022a001100214
20221-2-20221-3-2022a001105214
20221-3-20221-4-2022a001110230
20221-1-20221-2-2022a002105230
20221-2-20221-3-2022a002110230
20221-1-20221-2-2022a003120114
20221-1-20221-2-2022a004110114
20221-2-20221-3-2022a004110214

 

I have a dashboard in which I want to show whether there have been changes in the contract during the year

 I have a dashboard in which i want to who whether there have been changes in the contract during the year. If i select contract a001, it should show that on 01-02-2022 the selling price has been changed, and that on 01-03-2022 both selling price and payment term has been changed. How i can realize this?

 

Many thanks in advance,

 

Regards,

 

Frank

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your data model looks like or how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can give an idea on how to create and apply the measures to your data model.

 

Picture1.png

 

Selling price status: = 
VAR currentyear =
    MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
    SUMMARIZE ( RELATEDTABLE ( Data ), Data[Selling price], Data[Date start] )
VAR countsellingprice =
    COUNTROWS ( sellingpricetable )
RETURN
    IF (
        HASONEVALUE ( Contract_ID[Contract ID] ),
        IF (
            countsellingprice = 1,
            "NoChange / " & MAXX ( sellingpricetable, Data[Selling price] ),
            CONCATENATEX (
                sellingpricetable,
                Data[Selling price] & " " & Data[Date start],
                "->"
            )
        )
    )

 

Payment term status: = 
VAR currentyear =
    MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
    SUMMARIZE ( RELATEDTABLE ( Data ), Data[Payment term], Data[Date start] )
VAR countsellingprice =
    COUNTROWS ( sellingpricetable )
RETURN
    IF (
        HASONEVALUE ( Contract_ID[Contract ID] ),
        IF (
            countsellingprice = 1,
            "NoChange / " & MAXX ( sellingpricetable, Data[Payment term] ),
            CONCATENATEX (
                sellingpricetable,
                Data[Payment term] & " " & Data[Date start],
                "->"
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your data model looks like or how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can give an idea on how to create and apply the measures to your data model.

 

Picture1.png

 

Selling price status: = 
VAR currentyear =
    MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
    SUMMARIZE ( RELATEDTABLE ( Data ), Data[Selling price], Data[Date start] )
VAR countsellingprice =
    COUNTROWS ( sellingpricetable )
RETURN
    IF (
        HASONEVALUE ( Contract_ID[Contract ID] ),
        IF (
            countsellingprice = 1,
            "NoChange / " & MAXX ( sellingpricetable, Data[Selling price] ),
            CONCATENATEX (
                sellingpricetable,
                Data[Selling price] & " " & Data[Date start],
                "->"
            )
        )
    )

 

Payment term status: = 
VAR currentyear =
    MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
    SUMMARIZE ( RELATEDTABLE ( Data ), Data[Payment term], Data[Date start] )
VAR countsellingprice =
    COUNTROWS ( sellingpricetable )
RETURN
    IF (
        HASONEVALUE ( Contract_ID[Contract ID] ),
        IF (
            countsellingprice = 1,
            "NoChange / " & MAXX ( sellingpricetable, Data[Payment term] ),
            CONCATENATEX (
                sellingpricetable,
                Data[Payment term] & " " & Data[Date start],
                "->"
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.