cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Douglas_M
New Member

Comparing semi-additive calculations as a % change

I followed the excellent Semi Additive mesure guide at sqlbi.com (https://www.sqlbi.com/articles/semi-additive-measures-in-dax/) and now have a report that tracks balences accurately and the measure reports the most recent balance of the quarter. 

 

 

 

LastBalance Ever :=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR LastDates =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( Balances[Name] ),
ALL ( 'Date' )
),
"LastDay", CALCULATE (
MAX ( 'Balances'[Date] ),
ALL ( 'Date' ),
'Date'[Date] <= MaxDate
)
)
VAR LastDatesWithLineage =
TREATAS (
LastDates,
Balances[Name],
'Date'[Date]
)
VAR Result =
CALCULATE (
SUM ( Balances[Balance] ),
LastDatesWithLineage
)
RETURN
Result

 

I'm now keen to understand how I would compare changes between the most recent balence and the previous most recent balence. For example;

 

SupplierContract SpendEffective DateChange
A            10,000.0001/01/20190%
A            15,000.0001/01/2020150%
A            10,000.0002/01/202167%

 

Is there a DAX command that would allow me to compare with the previous last balence? Any ideas on how I could approach this?

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Douglas_M Please refer to my measure for this sample data:

 

Measure = MAX('Table'[Contract Spend]) / CALCULATE(MAX([Contract Spend]),FILTER(ALL('Table'),[Supplier]=MAX('Table'[Supplier]) && [Effective Date]<MAX([Effective Date])))

 


 
Updating Media


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
V-pazhen-msft
Community Support
Community Support

@Douglas_M Please refer to my measure for this sample data:

 

Measure = MAX('Table'[Contract Spend]) / CALCULATE(MAX([Contract Spend]),FILTER(ALL('Table'),[Supplier]=MAX('Table'[Supplier]) && [Effective Date]<MAX([Effective Date])))

 


 
Updating Media


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors