Calculating the difference using Max Value

[JL0101]

I have the code below that is a measure, where I want find the difference in weight based on the latest value and the previsous value however when I use the code below the coumn is blank, any help on this? Thanks 

a2 = 
VAR MaxRevision = MAX(Full_Rebar[Revision Number])
VAR PreviousRevision = MAX(Full_Rebar[Revision Number]) - 1
RETURN
     CALCULATE(SUM(Full_Rebar[weight_calculated_BS8666]),(Full_Rebar[Revision Number]=MaxRevision)-SUM(Full_Rebar[weight_calculated_BS8666]),(Full_Rebar[Revision Number]=PreviousRevision))

[AnalyticsWizard · Accepted solution]

@JL0101 

The issue with your measure is likely due to the way you're trying to subtract the SUM functions directly within the `CALCULATE` function, which isn't valid syntax in DAX. You need to compute the sums separately and then subtract them. Here’s how you can revise your measure to correctly calculate the difference in weight between the latest revision and the previous revision:

Weight Difference =
VAR MaxRevision = MAX(Full_Rebar[Revision Number])
VAR PreviousRevision = MaxRevision - 1
VAR WeightCurrent = CALCULATE(
SUM(Full_Rebar[weight_calculated_BS8666]),
Full_Rebar[Revision Number] = MaxRevision
)
VAR WeightPrevious = CALCULATE(
SUM(Full_Rebar[weight_calculated_BS8666]),
Full_Rebar[Revision Number] = PreviousRevision
)
RETURN
WeightCurrent - WeightPrevious

1. MaxRevision and PreviousRevision: These variables store the maximum revision number and the previous revision number respectively.

2. WeightCurrent and WeightPrevious: These variables calculate the total weight for the current and previous revisions using the `CALCULATE` function, which changes the context of the calculation to match the specified revision numbers.

3. Return Statement: Subtracts the previous revision's weight from the current revision's weight to find the difference.

This measure will return the difference in weight between the two specified revisions. Make sure that both revisions exist in your dataset; if the previous revision does not exist for some entries, the measure might return blank for those cases. This approach assumes that `Revision Number` is numeric and sequential.

If you encounter any more blanks, verify that `Revision Number` values are correctly inputted and that there are indeed records for both the maximum and the previous revisions in your dataset.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[JL0101]

Looks like the issue is that the previous revision is not

VAR PreviousRevision = MaxRevision - 1

Rather it is a the previous list (python term) item of 

Full_Rebar[Revision Number]

How would I get the previous list item of Full_Rebar[Revision Number]? 

[JL0101]

I had a closer look and for some reason WeightPrevious is coming up with no values i.e. blank ?

[JL0101]

Thank you!

[amitchandak]

@JL0101 , try like

a2 =
VAR MaxRevision = MAX(Full_Rebar[Revision Number])
VAR PreviousRevision = MAX(Full_Rebar[Revision Number]) - 1
RETURN
CALCULATE(SUM(Full_Rebar[weight_calculated_BS8666]),Full_Rebar[Revision Number]=MaxRevision)-calculate(SUM(Full_Rebar[weight_calculated_BS8666]),Full_Rebar[Revision Number]=PreviousRevision)

[JL0101]

I get the following error

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.