Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Date end | Contract ID | Selling price | Payment method | Payment term |
2022 | 1-1-2022 | 1-2-2022 | a001 | 100 | 2 | 14 |
2022 | 1-2-2022 | 1-3-2022 | a001 | 105 | 2 | 14 |
2022 | 1-3-2022 | 1-4-2022 | a001 | 110 | 2 | 30 |
2022 | 1-1-2022 | 1-2-2022 | a002 | 105 | 2 | 30 |
2022 | 1-2-2022 | 1-3-2022 | a002 | 110 | 2 | 30 |
2022 | 1-1-2022 | 1-2-2022 | a003 | 120 | 1 | 14 |
2022 | 1-1-2022 | 1-2-2022 | a004 | 110 | 1 | 14 |
2022 | 1-2-2022 | 1-3-2022 | a004 | 110 | 2 | 14 |
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
Solved! Go to Solution.
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],
"->"
)
)
)
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.
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],
"->"
)
)
)
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |