Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Falongi_82
Helper I
Helper I

TRENDLINE measure help

Hi all,

 

need your help to understand and correct a trendline measure found in the forum.

below my explanation 

thx in advance

pbi1_1.pngpbi1_2.png

 

MEASURE M_TRENDLINE_DATE :

My request :

How to correct the measure M_TRENDLINE_DATE to display in my table only the Date based on my filter and only have the calculation where I've a value in M_TOTAL_SAP_LINES?

M_TRENDLINE_ DATE = 
VAR Known =
FILTER (
SELECTCOLUMNS (
CALCULATETABLE ( VALUES ( CALENDAR_TABLE[Date] ), ALLSELECTED(Q_SAP_LINES_BY_FLOW) ),
"Known[X]", CALENDAR_TABLE[Date],
"Known[Y]", [M_TOTAL_SAP_LINES]
),
AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept = Average_Y
- Slope * Average_X
VAR Result = SUMX ( DISTINCT ( CALENDAR_TABLE[Date] ),
Intercept + Slope * CALENDAR_TABLE[Date]
)
RETURN
    IF(SELECTEDVALUE( CALENDAR_TABLE[Date] ) >= TODAY(),
Result,
        Result)

 

Thx in advance for your help,

Fabri

1 REPLY 1
Falongi_82
Helper I
Helper I

@OwenAuger ,

 

Hi Owen, 

Maybe you, you could help me?
I saw that you have already intervened for this kind of question.

Thx

Fabri

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors