Hello Marcelo,
There could be several reasons why DateDiffDays does not return the result.
Let us look through them one-by-one - I will use the date fields “Created at” and “Resolved at” for example purposes.
1) data import failure - data might not be retrieved at all
To check that - please create a report-specific calculated measure that retrieves the data of the particular property for the issue with the following MDX expression
[Issue].CurrentHierarchyMember.Get('Created at')
Please check if fields are imported for all issues in scope by adding the Issues dimension to the report rows and selecting these fields in the Measures dimension.
For the beginning - leave the default data format.
If you do not see any data - the field has not been retrieved.
2) data format mismatch - field imported as text
If you have imported the issue property as a text string - it will not change appearance when you switch from default to Integer or text, but it will turn into ‘mmm dd yyyy’ for Date/Time.
In that case, you might need to adjust the import settings to retrieve the field data in the correct format.
3) properties are used in the report and have specific formats
If you have both of the fields displayed in the report and have assigned a specific date format to the calculated measures - DateDiffDays might have difficulties parsing these values back into numbers for evaluations.
In the above picture, both fields have Date formats selected, and DateDiff cannot calculate the difference.
The solution is adding a direct reference to both fields to the DateDiffDays expression without the intermediate measures and formatting.
DateDiffDays(
[Issue].CurrentHierarchyMember.Get('Created at'),
[Issue].CurrentHierarchyMember.Get('Resolved at') )
4) property source is not in the report
A quite unlikely but still possible reason is not having the right base field for the property.
Since the fields ‘start of lock’ and ‘end of lock’ refer to a particular issue - you need to have the Issue dimension on your report rows.
If the Issue dimension member is not specified, it is presumed to be the default member - ‘All issues’ that does not have a specific value for these properties.
The solution lies in adding the Issue dimension to the report rows.
The above steps should have helped to localize the trouble with calculations.
In case if they didn’t - please send a direct e-mail to support@eazybi.com with the report definition and screenshots and we will look into it.
Kind regards,
Oskars / support@eazybi.com