Get the date when a measure cross x axis

Questions & Answers
,
1

I’m working on a calculated member that can get the date when another calculated member cross the x axis. But I cannot get that data point through MDX.

I though it would be something like:

Filter(
  [Measures].[Remaining estimated hours history Trend (Intermediate)],
  [Measures].[Remaining estimated hours history Trend (Intermediate)] < 0
).Item(0).Lag(0)

But I kept getting errors from this formula.

Thanks everyone that could help me.

2

Hi @Shenghan_Gao,

This calculation does not work as function Filter() requires a set of data over which to check criteria for measure value (https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/filter)
In your case, you are looking for a date when the trend is less than zero; then the set should be Time dimension members and criteria is measure for trend.

For example, to see in which month remaining estimated hour trend will be below zero, you might use a calculation like this:

Filter(
  --set of time members
  [Time].[Month].Members,
  --criteria for measure value
  [Measures].[Remaining estimated hours history Trend (Intermediate)] < 0
--return the first month when criteria are true
).Item(0).Name

You may go through the set of Days instead of the set of Month, but keep in mind that it might be quite heavy calculation as eazyBI will calculate a trend line for each date.

Best,
Zane / support@eazyBI.com