Forum Discussion
JenWilson
Helper II
Helper IIFilter out weekends from Dax Trend Line
Hi, is there something that I can add to the formula below that would NOT count weekends? I have 2 columns in my calendar table that ID weekends - Either a text column or a number column where Sat = 6 and Sun = 0, so count any dates that are between 1 and 5 is what I am hoping to use. I'm just not sure where to put it within the trend line formula below. Thanks for your help!
Trend Line Measure =
VAR Known =
Filter(
SELECTCOLUMNS(
ALLSELECTED ('Calendar'[Date]),
"Known[X]", 'Calendar'[Date],
"Known[Y]", [TotalHours],
"Known[Z]", [Department]
),
AND(AND(NOT(ISBLANK(Known[X])), NOT(ISBLANK(Known[Y]))),Known[Z]))
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
Return SUMX(DISTINCT(Calendar[Date]), Intercept + Slope * Calendar[Date])
Read about the NETWORKDAYS() convenience function.
1 Reply
- lbendlin
Super User
Read about the NETWORKDAYS() convenience function.