Forum Discussion
Calculating difference between two values in one column based on date and business line
- 6 years ago
Hi Anonymous
First of all, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Try this for a new calculated column in your table. Haven't tested it for lack of data in the proper format:
Difference = VAR PreviousDate_ = CALCULATE ( MAX ( Table1[Month] ), Table1[Month] < EARLIER ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Business Line] ) ) VAR PreviousValue_ = CALCULATE ( DISTINCT ( Table1[Actual] ), Table1[Month] = PreviousDate_, ALLEXCEPT ( Table1, Table1[Business Line] ) ) VAR CurrentValue_ = Table1[Actual] RETURN IF ( NOT ISBLANK ( CurrentValue_ ) && NOT ISBLANK ( PreviousValue_ ), CurrentValue_ - PreviousValue_ )
Hi Anonymous
First of all, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Try this for a new calculated column in your table. Haven't tested it for lack of data in the proper format:
Difference =
VAR PreviousDate_ =
CALCULATE (
MAX ( Table1[Month] ),
Table1[Month] < EARLIER ( Table1[Month] ),
ALLEXCEPT ( Table1, Table1[Business Line] )
)
VAR PreviousValue_ =
CALCULATE (
DISTINCT ( Table1[Actual] ),
Table1[Month] = PreviousDate_,
ALLEXCEPT ( Table1, Table1[Business Line] )
)
VAR CurrentValue_ = Table1[Actual]
RETURN
IF (
NOT ISBLANK ( CurrentValue_ ) && NOT ISBLANK ( PreviousValue_ ),
CurrentValue_ - PreviousValue_
)
Thank you! I have been looking for this approach. I've used it to calculate first derivative.