Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi! I need to calculate a 'simple' subtraction. I've got a visual table with products, and always the last 12 weeks with the calculate of their regresion in every week, and what i need its to calculate the regresion subtraction of the first and the last week who has any data (not blank).
week its a measure and regresion its a measure as well
Ex of the visual table:
product 12002 // 1st week regresion = 0.166 // [... rest of weeks...] // lastweek regresion= 0.142
what i need:
measure (for every product) = 1st week regresion (not blank) - lastweek regresion (not blank) = 0.024
how can i get this in a measure?
thanks in advance!
Hi again @ivanmoreno
Per my understanding the solution shall be something like this:
First activate the column totals from the format pane.
Then remove the current regression measure from the matrix and add the following measure instead:
=
IF (
HASONEVALUE ( TableName[Week Number] ),
[Regression],
VAR T1 =
ADDCOLUMNS ( VALUES ( TableName[Week Number] ), "@Regression", [Regression] )
VAR T2 =
FILTER ( T1, [@Regression] <> BLANK () )
VAR MaxWeek =
MAXX ( T2, TableName[Week Number] )
VAR MinWeek =
MINX ( T2, TableName[Week Number] )
VAR MaxWWeekValue =
MAXX ( FILTER ( T2, TableName[Week Number] = MaxWeek ), [@Regression] )
VAR MinWWeekValue =
MINX ( FILTER ( T2, TableName[Week Number] = MinWeek ), [@Regression] )
RETURN
MaxWWeekValue - MinWWeekValue
)
Hi @ivanmoreno
what do you mean by "week its a measure"? Isn't it a column? Do you have a date table?
User | Count |
---|---|
106 | |
86 | |
81 | |
74 | |
71 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |