Forum Discussion
Solved
Help me with DAX
Hi,
I have following table called Table1 as below,
| Model Code | MSRP |
| FJ11 | 120000 |
| FJ12 | 136000 |
| FJ13 | 219800 |
| FJ14 | 260000 |
| FJ15 | 325670 |
I want to subtract FJ11(MSRP) from FJ12(MSRP), FJ12(MSRP) from FJ13(MSRP) and so on.
FJ11 Corresponsing cell should be Blank. Please see below for desired output and kindly help me DAX code. Thank you in advance.
| Model Code | MSRP | LC |
| FJ11 | 120000 | 0 |
| FJ12 | 136000 | 16000 |
| FJ13 | 219800 | 83800 |
| FJ14 | 260000 | 40200 |
| FJ15 | 325670 | 65670 |
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
OFFSET function (DAX) - DAX | Microsoft Learn
LC Calculated Column = VAR _currentrow = 'Table 1'[MSRP] VAR _previousrow = MAXX ( OFFSET ( -1, 'Table 1', ORDERBY ( 'Table 1'[Model Code], ASC ), , , MATCHBY ( 'Table 1'[Model Code] ) ), 'Table 1'[MSRP] ) RETURN IF ( NOT ISBLANK ( _previousrow ), 'Table 1'[MSRP] - _previousrow )
2 Replies
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
OFFSET function (DAX) - DAX | Microsoft Learn
LC Calculated Column = VAR _currentrow = 'Table 1'[MSRP] VAR _previousrow = MAXX ( OFFSET ( -1, 'Table 1', ORDERBY ( 'Table 1'[Model Code], ASC ), , , MATCHBY ( 'Table 1'[Model Code] ) ), 'Table 1'[MSRP] ) RETURN IF ( NOT ISBLANK ( _previousrow ), 'Table 1'[MSRP] - _previousrow )you can also try this
Column =VAR _last=maxx(FILTER('Table','Table'[Model Code]<EARLIER('Table'[Model Code])),'Table'[Model Code])return if (_last="",BLANK(),'Table'[MSRP]-maxx(FILTER('Table','Table'[Model Code]=_last),'Table'[MSRP]))
