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 year Date start ...
  • Jihwan_Kim's avatar
    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],
                    "->"
                )
            )
        )