Getting unexpected data from measure based on the date hierarchy level

[kdarnt]

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

[kdarnt]

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. 

[kdarnt]

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. 

[v-rzhou-msft · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.