It is often noted that the problem of a DAX measure dynamically shows percent change, but no value is usually found in some core areas. Here are a few common causes and troubleshooting methods:
Blank or Missing Values in Data:
If any of the values you are using in your DAX measure is empty or null, the calculation will almost return blank as a result. Ensure that the columns or measures that participate in the computation are actually populated with real data. IF or COALESCE functions could be used to deal with null and blank values so that the measure could calculate even when some data is missing.
Example:
DynamicPercentChange =
IF( I
SBLANK([CurrentValue]) || ISBLANK([PreviousValue]),
BLANK(),
([CurrentValue] - [PreviousValue]) / [PreviousValue]
)
DAX measures are context-sensitive; use them carefully. Thus, any slicer or filter must be accurately applied before the measure is evaluated in the correct context. Ensure that the slicers currently select the data and that no conflicting assessment leads to an unexpected blank result.
Employing CALCULATE and FILTER Functions:
Having changed the context for the operation on the dynamic percent change measure, though the CALCULATE or FILTER functions would help, inappropriate use might still result in blank remainders. If it considers time intelligence (say, current as compared with the previous), make sure that the time context is correctly defined and the values are calculated properly over the time span.
Illustration for time sensitivity:
DynamicPercentChange =
CALCULATE(
DIVIDE(
[CurrentValue] - [PreviousValue],
[PreviousValue]
),
DATEADD('Date'[Date], -1, YEAR)
)
Division by Zero: If you are actually dividing by zero, that is, the previous value is zero, the result will return blank. Always use the safe technique of DIVIDE in place of / to handle divide-by-zero situations.
DynamicPercentChange =
DIVIDE(
[CurrentValue] - [PreviousValue],
[PreviousValue],
0 // Return 0 instead of error if division by zero )
Check for Gaps in Data: DAX may not give a valid result if there are gaps in your data (say, a month or a day is missing). Incomplete data models in production, where rows are actually absent from the data set, can severely impact the calculation of a percent change.
With those common issues, problems with finding the reason for the blank value in your DAX measure should be solved effectively.
