topic Re: Calculate variance to previous month payment in DAX Commands and Tips

Calculate variance to previous month payment

Lunge_PTP — Wed, 28 Jul 2021 20:12:30 GMT

I have a table with persons and amount paid pr month

I would like calculate the change of amount to previous amount for each record

Table:

Person	Month YY-MM	Amount	Calculation
123	2021-05	50	0
123	2021-06	100	50
123	2021-07	100	0
123	2021-08	75	-25
345	2021-05	100	0
345	2021-06	75	-25
345	2021-07	75	0
345	2021-08	50	-25

Looking forward for your inputs.

Re: Calculate variance to previous month payment

wdx223_Daniel — Thu, 29 Jul 2021 00:49:16 GMT

Re: Calculate variance to previous month payment

Jihwan_Kim — Thu, 29 Jul 2021 05:30:15 GMT

Amount measure : =

SUM(Sales[Amount])

Diff measure : =

VAR _currentperson =

MAX ( Person[Person] )

VAR _currentmonthsort =

MAX ( 'Calendar'[YY-MM Sort] )

VAR _previousmont =

CALCULATE (

MAX ( 'Calendar'[Month YY-MM] ),

FILTER ( ALL ( 'Calendar' ), 'Calendar'[YY-MM Sort] < _currentmonthsort )

)

VAR _previousamount =

CALCULATE (

[Amount measure :],

FILTER (

ALL ( Sales ),

Sales[Person] = _currentperson

&& Sales[Month YY-MM] = _previousmont

)

RETURN

IF (

HASONEVALUE ( Person[Person] ) && NOT ISBLANK ( _previousamount ),

[Amount measure :] - _previousamount

)

Link to the sample pbix file

Re: Calculate variance to previous month payment

Lunge_PTP — Thu, 29 Jul 2021 07:30:42 GMT

Thanks wdx223_Daniel and Jihwan_Kim,
both solutions works fine, I'm still in the development of my Dashboard, so I'm not able to measure performance on actual data, with several thousands of lines.