Forum Discussion

ivanmoreno's avatar
ivanmoreno
Regular Visitor
4 years ago

help to calculate data from measures

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!

Need Help

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi ivanmoreno 

    what do you mean by "week its a measure"? Isn't it a column? Do you have a date table?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
)