Reference measure

[Anonymous]

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.

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

[Anonymous · Accepted solution]

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

[Ashish_Mathur]

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]

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

[Ashish_Mathur]

You must have a Calendar Table.

[Anonymous]

Hi Arthur,

I tried with calendar table but it is changing by date slicer.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.

[Anonymous]

https://drive.google.com/file/d/1AICwFILUHE_B3QxmkCnCDWABzH1X_6dq/view?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi,

In 2rd month or in 3rd month we can't see the "A003" of baseline.

[Ashish_Mathur]

Hi,

Download the revised PBI file from here.

Hope this helps.

[Anonymous]

Hi,

It is almost good, there is only a small issue that we show "A003" in first month but there is no data for this in January. 

In this case enough 1 measure:

Baseline 2 =

VAR First_Date_Available = CALCULATE(MIN(Indicators_Value[Date]),ALL('Calendar'))

RETURN

CALCULATE(AVERAGE(Indicators_Value[Value_%]),datesbetween('Calendar'[Date],First_Date_Available,First_Date_Available))

[Ashish_Mathur]

Hi,

Modify my measure to

Baseline 1 = if(ISBLANK(AVERAGE(Indicators_Value[Value_%])),BLANK(),CALCULATE(AVERAGE(Indicators_Value[Value_%]),datesbetween('Calendar'[Date],[Date on which value is first available],[Date on which value is first available])))

Hope this helps.

[Anonymous]

Hi,

But I think not necessary to use auxiliary table with date. Are you agree? 

Spoiler

Baseline 1 =

VAR Date_on_which_value_is_first_available = CALCULATE(MIN(Indicators_Value[Date]),ALL('Indicators_Value'))

RETURN

IF(

ISBLANK(AVERAGE(Indicators_Value[Value_%])),

BLANK(),

CALCULATE(AVERAGE(Indicators_Value[Value_%]),

DATESBETWEEN('Indicators_Value'[Date],Date_on_which_value_is_first_available,Date_on_which_value_is_first_available)))

---

[Anonymous]

Hi,

Yes, now it is working!

[lbendlin]

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

[Anonymous]

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