Forum Discussion

frankhofmans's avatar
frankhofmans
Helper IV
4 years ago
Solved

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

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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.

     

     

    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],
                "->"
            )
        )
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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.

     

     

    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],
                "->"
            )
        )
    )