Forum Discussion
Calculating the difference using Max Value
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 👍
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 👍
Thank you!