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
ivanmoreno
Regular Visitor

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

 

art.png

 

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!

2 REPLIES 2
tamerj1
Super User
Super User

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
)
tamerj1
Super User
Super User

Hi @ivanmoreno 

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

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.