Forum Discussion

kdarnt's avatar
kdarnt
Frequent Visitor
4 years ago
Solved

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

 

  • Anonymous's avatar
    Anonymous
    4 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 Zhou

     

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

3 Replies

  • kdarnt's avatar
    kdarnt
    Frequent 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. 

     

     

    • Anonymous's avatar
      Anonymous
      Not 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 Zhou

       

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

  • kdarnt's avatar
    kdarnt
    Frequent 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.