Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TotunG
Resolver I
Resolver I

Comparing Values with Offset?

Hey,

 

I'd like to create a measure that can help me find the difference between 2 values but not sure of the best way to do it.

 

I have a week slicer that goes from 1-6 and I'd like to create a measure that compares the latest value so like Week 6 with the other largest selected slicer value. 

 

So if slicer had WK6, WK2, WK4 selected: Comparison would be WK6-WK4

If slicer had WK4, WK2, WK1 selected: Comparison would be WK4-WK2

If slicer had WK5, WK4, WK3 selected: Comparison would be WK5-WK4

 

How can I get this consistent everytime?

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @TotunG ,

 

Here are the steps you can follow:

1. You reposition the two columns of the Period Ref table, with [Period Ref] first and [Period Name] last.

vyangliumsft_0-1686551585645.png

2. Create calculated column.

Table 2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Period Ref],
    "Period Name",
    "P_Total")
return
UNION(
    'Table',_table1)

vyangliumsft_1-1686551585650.png

3. Combine the relationship between two tables.

vyangliumsft_2-1686551646612.png

4. Create measure.

Flag=
IF(
    MAX('Table 2'[Period Ref]) in SELECTCOLUMNS('MainData',"1",[Period]) && MAX('Table 2'[Period Name]) <> "P_Total",
    [Total Value],[Dynamic DIfference])

5. Result:

vyangliumsft_3-1686551646613.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

Hi  @TotunG ,

Table 2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Period Ref],
    "Period Name",
    "P_Total")
return
UNION(
    'Table',_table1)

This allows the new table to be appended to the previous table using the Union() formula, forming

UNION function (DAX) - DAX | Microsoft Learn

SUMMARIZE function (DAX) - DAX | Microsoft Learn

vyangliumsft_0-1686723013405.png

 

This will add a column of p_Total when it is put into the matrix

vyangliumsft_1-1686723013405.png

This adds a column of p_Total when it is put into the matrix

Flag=IF(    MAX('Table 2'[Period Ref]) in SELECTCOLUMNS('MainData',"1",[Period]) && MAX('Table 2'[Period Name]) <> "P_Total",    [Total Value],[Dynamic DIfference])

SELECTCOLUMNS('MainData',"1",[Period]):

Returns a table that contains the columns of [Period]

 

This is used to determine whether 'Table 2[Period ref] exists in the [Period] of Main Data Table and the [Period Name] of Table2 is not the character "p_Total", if everything matches, then it is [Total Value], otherwise the result is [Dynamic DIfference])

IF function (DAX) - DAX | Microsoft Learn

SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yangliu-msft
Community Support
Community Support

Hi  @TotunG ,

 

Here are the steps you can follow:

1. You reposition the two columns of the Period Ref table, with [Period Ref] first and [Period Name] last.

vyangliumsft_0-1686551585645.png

2. Create calculated column.

Table 2 =
var _table1=
SUMMARIZE(
    'Table','Table'[Period Ref],
    "Period Name",
    "P_Total")
return
UNION(
    'Table',_table1)

vyangliumsft_1-1686551585650.png

3. Combine the relationship between two tables.

vyangliumsft_2-1686551646612.png

4. Create measure.

Flag=
IF(
    MAX('Table 2'[Period Ref]) in SELECTCOLUMNS('MainData',"1",[Period]) && MAX('Table 2'[Period Name]) <> "P_Total",
    [Total Value],[Dynamic DIfference])

5. Result:

vyangliumsft_3-1686551646613.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Seems like it might work - will try this out. Do you mind briefly breaking down the steps in this and why you did them? No worries if not as appreciate your help regardless.

v-yangliu-msft
Community Support
Community Support

Hi  @TotunG ,

 

It's not clear what your table structure is, you can try placing the formula in a PBIX file to see if that works.

If the results are not what you expected, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Okay sample file is attached with explaination (also a photo if needed). There is a relationship between 2 tables with columns Period and Period Name so that user can use Period Name to filter MainData periods:

TotunG_0-1686129390299.png

https://drive.google.com/file/d/19KDNuA7CRElc-O1H8WwschGNoU29qUBv/view?usp=sharing

v-yangliu-msft
Community Support
Community Support

Hi  @TotunG ,

I created some data:

vyangliumsft_0-1686123467744.png

 

Here are the steps you can follow:

1. Create measure.

Measure =
var _select=
SELECTCOLUMNS('Table',"1",[Week_Slicer])
var _maxx=
MAXX(
    FILTER(ALL('Table'),'Table'[Week_Slicer] in _select),[Week])
var _secondlargest=
MAXX(
    FILTER(ALL('Table'),'Table'[Week_Slicer] in _select&&'Table'[Week]<_maxx),[Week])
return
SUMX(
    FILTER(ALL('Table'),'Table'[Week]=_maxx),[Value])
    -
SUMX(
    FILTER(ALL('Table'),'Table'[Week]=_secondlargest),[Value])

2. Result:

vyangliumsft_1-1686123467746.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank you - would this still work if Week table is different from the Data table but connected with a relationship and the value being filtered is a measure?

lbendlin
Super User
Super User

should be possible, even without offset.  Ideally you would have numeric week numbers though, otherwise you run into trouble comparing WK10 with WK6 (for example)

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.