Forum Discussion

Uzi2019's avatar
Uzi2019
Community Champion
3 years ago
Solved

Previous Row value DAX

Hi Expert,

Need your urgent support on  DAX. I want to get the Output value like belowlogic.

First Output Row = Amount - Billed Amount

Second Output Row  = First Output Row - Current Row Billed Amount

So on...

Brand

Order ID

Start Date

End Date

Order Date

Amount

Billed Amount

Output

AAA

1234567

24-Apr-23

01-May-23

24-Apr-23

100

10

100-10 = 90

AAA

1234567

24-Apr-23

01-May-23

25-Apr-23

200

20

(90)-20=70

AAA

1234567

24-Apr-23

01-May-23

26-Apr-23

140

10

(70)-10=60

AAA

1234567

24-Apr-23

01-May-23

27-Apr-23

200

30

(60)-30=30

AAA

1234567

24-Apr-23

01-May-23

28-Apr-23

100

40

(30)-40=-10

AAA

1234567

24-Apr-23

01-May-23

29-Apr-23

120

40

(-10)-40=-50

AAA

1234567

24-Apr-23

01-May-23

30-Apr-23

210

20

(-50)-20=-70

AAA

1234567

24-Apr-23

01-May-23

01-May-23

100

10

(-70)-10 = -80

 

Any help would be highly appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Uzi2019 ,

    Please hvae a try.

    Create a measure.

    Measure =
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[Billed Amount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ORDER DATE] <= SELECTEDVALUE ( 'Table'[ORDER DATE] )
                && 'Table'[Brand ] = SELECTEDVALUE ( 'Table'[Brand ] )
                && 'Table'[Order ID] = SELECTEDVALUE ( 'Table'[Order ID] )
        )
    )
VAR _MINDATE =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Brand ] = SELECTEDVALUE ( 'Table'[Brand ] )
                && 'Table'[Order ID] = SELECTEDVALUE ( 'Table'[Order ID] )
        ),
        'Table'[ORDER DATE]
    )
VAR _MIN =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ORDER DATE] = _MINDATE )
    )
RETURN
    _MIN - _1

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Uzi2019 ,

    Please hvae a try.

    Create a measure.

    Measure =
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[Billed Amount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ORDER DATE] <= SELECTEDVALUE ( 'Table'[ORDER DATE] )
                && 'Table'[Brand ] = SELECTEDVALUE ( 'Table'[Brand ] )
                && 'Table'[Order ID] = SELECTEDVALUE ( 'Table'[Order ID] )
        )
    )
VAR _MINDATE =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Brand ] = SELECTEDVALUE ( 'Table'[Brand ] )
                && 'Table'[Order ID] = SELECTEDVALUE ( 'Table'[Order ID] )
        ),
        'Table'[ORDER DATE]
    )
VAR _MIN =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ORDER DATE] = _MINDATE )
    )
RETURN
    _MIN - _1

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.