Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Reference measure

Dear all, I would like to use a measure to calculate baseline values. In this case, the baseline is the data of the first date on each indicator (and each time, so it is corrected), blue background...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    My solution:

    Baseline ?
    VAR MIN_MONTH to MONTH(CALCULATE(MIN(Indicators_Value[Date].[ Closes]),FILTER(ALL(Indicators_Value),Indicators_Value[Indicator Id] - SELECTEDVALUE(Indicators_Value)))
    Return
    CALCULATE(
    AVERAGE(
    Indicators_Value[Value_%]),
    FILTER(
    ALL(Indicators_Value),
    Indicators_Value[ID del indicador] - SELECTEDVALUE(Indicators_Value[Indicator Id]) && Indicators_Value[Fecha]. [MesNo]-MIN_MONTH))
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

use MINX() and expand the table filter to ALL(Table) to escape your current filter context.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    lbendlin 

    Hi,

     

    It is working with MINX. But just partly. Because the code find the first year, month and day and it is not changed by the slicer but it is shows everytime 1/1/2020 and what I need maybe 2/8/2020 or 1/18/2020 or other date. Depends on the indicator. Do you have any idea?

     

    Baseline =
    CALCULATE(
    AVERAGE(
    Indicators_Value[Actual_Cummulative]),
    FILTER(
    Indicators_Value, [MINX_Year] = Indicators_Value[Date].[Year]),
    FILTER(
    Indicators_Value, [MINX_Month] = Indicators_Value[Date].[MonthNo]),
    FILTER(
    Indicators_Value, [MINX_Day] = Indicators_Value[Date].[Day])
    )
     
    MINX_Day = MINX(FILTER(ALL(Indicators_Value),MIN(Indicators_Value[Date].[Day])), Indicators_Value[Date].[Day])
    MINX_Month = MINX(FILTER(ALL(Indicators_Value),MIN(Indicators_Value[Date].[MonthNo])), Indicators_Value[Date].[MonthNo])
    MINX_Year = MINX(FILTER(ALL(Indicators_Value),MIN(Indicators_Value[Date].[Year])), Indicators_Value[Date].[Year])