Forum Discussion
Solved
How identify change vs previous row?
hi all,
i have a customer database which logs all contract changes. Fe:
| Start_Date | End_Date | Customer ID | Sales price product A | Sales price product B | Sales price product C | fee % | Change in sales price product B? |
| 1-jan-22 | 31-jan-22 | A001 | 100 | 120 | 150 | 10% | No |
| 1-feb-22 | 31-mrt-22 | A001 | 100 | 150 | 150 | 10% | Yes |
| 1-apr-22 | 30-jun-22 | A001 | 110 | 160 | 160 | 10% | Yes |
| 1-jul-22 | A001 | 110 | 160 | 170 | 12% | No | |
| 1-jan-22 | 31-jan-22 | A002 | 110 | 125 | 160 | 11% | No |
| 1-feb-22 | A002 | 115 | 125 | 160 | 12% | No | |
| 1-jan-22 | A003 | 112 | 123 | 150 | 11% | No | |
| 1-jan-22 | 31-jan-22 | A004 | 100 | 105 | 110 | 15% | No |
| 1-feb-22 | 30-jun-22 | A004 | 110 | 105 | 115 | 15% | No |
| 1-jul-22 | A004 | 110 | 110 | 115 | 15% | Yes | |
| 1-jan-22 | 31-jul-22 | A005 | 140 | 140 | 125 | 12% | No |
| 1-aug-22 | A005 | 140 | 150 | 150 | 12% | Yes | |
| 1-jan-22 | 31-jul-22 | A006 | 140 | 145 | 170 | 15% | No |
| 1-aug-22 | A006 | 150 | 145 | 170 | 16% | No |
i want to create a extra column (change in sales price product B). When the price of product B (for the same customers) changes, it's a Yes, otherwise a No.
Does anyone has a solution for this?
Many thanks,
Regards, Frank
Hi frankhofmans
You can create a new column with this DAX formula
Change in product B? = VAR _previousStartDate = MAXX(FILTER('Table','Table'[Start_Date]<EARLIER('Table'[Start_Date]) && 'Table'[Customer ID]=EARLIER('Table'[Customer ID])),'Table'[Start_Date]) VAR _previousPrice = MAXX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID]) && 'Table'[Start_Date] = _previousStartDate),'Table'[Sales price product B]) RETURN IF(_previousPrice=BLANK(),"No",IF(_previousPrice='Table'[Sales price product B],"No","Yes"))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
Hi frankhofmans
You can create a new column with this DAX formula
Change in product B? = VAR _previousStartDate = MAXX(FILTER('Table','Table'[Start_Date]<EARLIER('Table'[Start_Date]) && 'Table'[Customer ID]=EARLIER('Table'[Customer ID])),'Table'[Start_Date]) VAR _previousPrice = MAXX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID]) && 'Table'[Start_Date] = _previousStartDate),'Table'[Sales price product B]) RETURN IF(_previousPrice=BLANK(),"No",IF(_previousPrice='Table'[Sales price product B],"No","Yes"))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.