Measure to calculate a slope factor

[Cado_one]

Hi,

I think my problem must be easy to solve but I don't manage to find my error.

I have a some production values (EnergyInvoiced) displayed over time axis (mois) and I wish to display on a card the trend line slope factor (coefficient directeur in french).

I created a measure to calculate this factor but it returns only "Blank" : 

Coefficient = (CALCULATE(SUM(MonthlyData[EnergyInvoiced]),LASTDATE(MonthlyData[mois])) - CALCULATE(SUM(MonthlyData[EnergyInvoiced]),FIRSTDATE(MonthlyData[mois]))) / DATEDIFF(LASTDATE(MonthlyData[mois]),FIRSTDATE(MonthlyData[mois]),YEAR)

Does someone has an idea of what could be wrong in the formula ? And how to solve the problem ?

Thanks in advance,

Cado

[Syndicate_Admin]

Hello, did anyone find out??

[v-lionel-msft]

Hi @Cado_one ,

The reason why the measure result is negative is because the X-axis is calculated as a negative value.

You only need to exchange the positions of the two parameters in the DATEDIFF() function.

Best regards,
Lionel Chen

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

[Cado_one]

Hi @v-lionel-msft 

I think there is a misunderstanding on the objective of the measure. Your measure calculates the slope factor of a line passing by the first and the last value of the data (blue line of your report), what I need is the slope factor of the trend line (red dashed line of your report)

For example in the screenshot below, when filtering the date on the period 2003-2013 you measure returns a factor of -10% (value on lastdate - value on first date)/(lastdate-firstdate) = (7-6)/(2013-2003) = -0,1 = 10%

What I need is (12 - 4)/(2013-2003) = 8/10 = 0,8

Sorry for my bad english I have difficulties descripting precisely my need, if it's not clear don't hesitate asking me questions.

Best regards

Cado

[Cado_one]

Hi @v-lionel-msft 

I wish you a happy new year !

Did you thought any update tot his issue ?

Thanks in advance,

Cado

[v-lionel-msft]

Hi @Cado_one ,

Please refer to my formula.

Measure = 
VAR __min = MINX(ALLSELECTED(Sheet1), [Date] )
VAR __max = MAXX(ALLSELECTED(Sheet1), [Date] )
RETURN
VAR x = 
SUMX(
    FILTER( ALL(Sheet1), [Date] = __min ),
    [Value]
) 
VAR y = 
SUMX(
    FILTER( ALL(Sheet1), [Date] = __max ),
    [Value]
)
RETURN
(y- x)/
DATEDIFF(
    __max, __min,
    YEAR
)

Best regards,
Lionel Chen

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

[Cado_one]

Hi @v-lionel-msft 

Thanks for helping me.

I think there is a misunderstood or there is a problem with your formula. Your trend line is climbing so the factor should be positive. If we calculate it manually between 2003 and 2013 for example we would have :

factor = (12 - 5)/(2013-2003) = 7/10 = 70% --> the value increase by 0,7/year

Moreover, I need this factor to adapt automatically if I change the date period (with a slicer).

How can we deal with that ?

Best regards,

Cado