Forum Discussion

TotunG's avatar
TotunG
Icon for Resolver I rankResolver I
3 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

    2. Create calculated column.

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

    3. Combine the relationship between two tables.

    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:

     

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

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

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.