Unexpected results with custom date fields and calculated measures in eazyBI

Question

I am really struggling to figure out from the documentation what I am doing wrong here.

Planned, Expected, and Actual are custom date fields I've been using forever on our stories, and Plan-to-Exp and Exp-to-Act are new calculated measures. There are two issues in my eazyBI report.

First, the dates themselves are not displaying as dates, as is obvious from the screenshot.

I believe I have imported the fields correctly:

But I cannot figure out why this MDX is displaying some integer rather than an actual date:

Second, the calculation seems way off when I attempt to manually verify.

In that first row, Plan-to-Exp is correctly calculated as 5, but Exp-to-Act should be 4 not 32.

DateDiffWorkdays([Issue].CurrentHierarchyMember.Get('Planned Date'), [Issue].CurrentHierarchyMember.Get('Expected Date'))

DateDiffWorkHours([Issue].CurrentHierarchyMember.Get('Expected Date'), [Issue].CurrentHierarchyMember.Get('Handoff Date'))

All of this seems awfully simple for me to get wrong, but I'm really struggling to find an adequate explanation in the eazyBI documentation.

Roberts Čāčus - eazyBI · Accepted Answer

Hi @Paul Garrett ,

You have imported the fields correctly. But when creating new calculated measures, eazyBI sometimes guesses the default formatting incorrectly. Update the formatting of all three calculated measures, "Planned Handoff", "Expected Handoff", and "Actual Handoff", to dates. See more details here - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members#Calculatedmeasuresandmembers-Formatting.

You can also use the default date properties, e.g., "Issue Planned Date". Those should have the correct formatting  already applied.

For your second problem, it seems you have used the DateDiffWorkHours() function for "Exp-to-Act" instead of DateDiffWorkdays(). Update the formula with the correct function, and you should have the expected result.

Best,
Roberts // support@eazybi.com