Forum Discussion
Measure that ignores certain slicer
Hello, guys! I'm new to dax, but that's what I want to do:
I want to compare values from actual month's sales vs trend line.
The 1st problem was to get values from trend line. ok, I found formula for measure on this forum. Here it is in my model:
VT_TrendLine =
VAR Known =
FILTER (
SELECTCOLUMNS (
CALCULATETABLE ( VALUES ( 'all_in_local'[Date] ), ALL ('all_in_local') ),
"Known[X]", ('all_in_local'[Date]),
"Known[Y]", [F_Sales_Value]
),
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
RETURN
SUMX ( DISTINCT ( 'all_in_local'[Date] ),
Intercept + Slope * 'all_in_local'[Date]
)Ok, that's awesome! Now I have a trend line, that fits PowerBI's trend line and has actual values. But there is the problem. I cant get the last value from it because it uses dates in calculations and I have a date Slicer in my report. So as the slicer is used - all trend line data becomes invalid. How can I freeze dates in this calculation? The thing is, I have a few slicers on my page, and i need to moke it ignore only one - date one. Thank you, guys!
1 Reply
- jshutters
Resolver I
Have you tried enhancing your measure with the FILTER function using ALL? For example...
... RETURN
CALCULATE( <your expression>,
FILTER( ALL('all_in_local'), <any additional filters> ))