Forum Discussion
Getting unexpected data from measure based on the date hierarchy level
Hi, I have a date hierarchy that is performing as expected for one measure, but giving some odd results for another measure.
Here's my two charts, joined by date.
This is what the date table looks like (apologies for not uploading actual data, it's HR data so sensitive)
Date hierarchy:
Here are the two measures - I am having trouble with the chart displaying the second one, Monthly_turnover:
**Here's the actual issue. When I chart the Reg_employees_month_beg using the custom date hierarchy, it works as expected (top chart). But the chart of the Monthly_turnover measure (which relies on the Reg_employees_month_beg) measure - it only works at the Month level of the hierarchy. When I add Year back in, now the turnover values are infinity. Since something divided by zero is infinity, that makes me think something is going wrong with the denominator of the Monthly_turnover measure, but it seems to be working from the first chart. (The Reg_employees_month_end measure is also working as expected.)
Chart with year and month in date hierarchy - and wrong values for Monthly_turnover:
Chart showing expected values (ballpark) for Monthly_turnover, but I need to see it by year and month.
Thanks in advance
- Anonymous4 years ago
Hi kdarnt ,
[Monthly_turnover] measure is based on [Term],[Reg_employees_month_beg] and [Reg_employees_month_end]. According to your screenshoot, I think [Monthly_turnover] will return 0 or blank when [Reg_employees_month_beg] + [Reg_employees_month_end] = 0 or [Term] = 0. So please check [Term] and [Reg_employees_month_beg] + [Reg_employees_month_end] part to get more details.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- kdarntFrequent Visitor
I should mention the Terms measure referenced in the turnover calculation also seems to be working fine in charts, with both the year and month parts of the date hierarchy.
- AnonymousNot applicable
Hi kdarnt ,
[Monthly_turnover] measure is based on [Term],[Reg_employees_month_beg] and [Reg_employees_month_end]. According to your screenshoot, I think [Monthly_turnover] will return 0 or blank when [Reg_employees_month_beg] + [Reg_employees_month_end] = 0 or [Term] = 0. So please check [Term] and [Reg_employees_month_beg] + [Reg_employees_month_end] part to get more details.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- kdarntFrequent Visitor
Thanks, yeah I finally put it together that the values were being calculated as 0 and infinity, which was messing up the axis of the chart.