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 fields.

abanar_0-1602747264247.png

I tried to solve the problem, but unfortunately it is not working because if I change the date with the slicer, the baseline is also changing. Can you help me?

Baseline ?
CALCULATE(
AVERAGE(
Indicators_Value[Value_%]),
FILTER(
Indicators_Value, [Min_Year] to Indicators_Value[Date]. [Year]),
FILTER(
Indicators_Value, [Min_Month] to Indicators_Value[Date]. [Month]),
FILTER(
Indicators_Value, [Min_Day] to Indicators_Value[Date]. [Day])
)
Note:
Min_Day - MIN(Indicators_Value[Date].[ Day])

Min_Month - MIN(Indicators_Value[Date].[ Month])

Min_Year - MIN(Indicators_Value[Date].[ Year])

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))

16 Replies

  • lbendlin's avatar
    lbendlin
    Super 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])
       
       
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Try this

    Baseline = CALCULATE(AVERAGE(Indicators_Value[Value_%]),datesbetween(calendar[date],minx(all(calendar),calendar[date]),minx(all(calendar),calendar[date])))
    Hope this helps.
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

       

      Not yet. 

      Baseline = CALCULATE(AVERAGE(Indicators_Value[Value_%]),datesbetween(calendar(Indicators_Value[Date],minx(all(calendar),calendar(Indicators_Value[Date]),minx(all(calendar),calendar(Indicators_Value[Date])))
  • Anonymous's avatar
    Anonymous
    Not applicable
    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))