DAX OFFSET Returns An Error When a Field is changed in the Source

[tecumseh]

Hi all,

The DAX below works as expected while I use the Net Revenue field.
However if I update all references from Net Revenue to Gross Revenue, I receive an error message:

Error Message:
<ccon>OLE DB or ODBC error: Query (28, 13) Despite of MatchBy columns being specified, duplicated rows are encountered in OFFSET's Relation parameter. This is not allowed.

Seems odd since I am not changing anything in the OFFSET Parameters in the DAX Formula.
Any thoughts?

Thanks,
w

Test_T01.1 = 
/*
* 
*/

VAR T10 = 
SELECTCOLUMNS(
SUMMARIZE(
FILTER(
Sales,
Sales[Channel] = "Education" &&
Sales[Advertiser] <> "Accounting Adjustment" &&
Sales[Net Revenue] > 0
),
Sales[Customer],
Sales[Sales Person],
Sales[Date],
"Revenue",[Net Revenue]
),
"Customer", Sales[Customer],
"Sales Person", Sales[Sales Person],
"Date", Sales[Date],
"Revenue", Sales[Net Revenue]
)

VAR T20 = 
ADDCOLUMNS(
T10,
"Previous Date",
SELECTCOLUMNS(
OFFSET(
-1,
T10,
ORDERBY([Date], ASC),
PARTITIONBY( [Customer], [Sales Person] ),
MATCHBY( [Customer], [Sales Person], [Date] )
),
[Date]
)
)

RETURN
T20

[Ahmedx]

Greg Decler is correct in his statement, but nevertheless, share a sample PBIX file so that we can help you

[Greg_Deckler]

@tecumseh There is nothing that OFFSET can do that can't be achieved without it using a simple FILTER statement. I would avoid that function, it often has issues that are almost inexplicable.

[tecumseh]

Thanks @Greg_Deckler ,

Is there a video or article that you could point me to that achieves the reults I am looking for using the FILTER statement?

Thanks,

w

[Greg_Deckler]

@tecumseh The last third of this video explores OFFSET pretty in-depth. Starting around the 18 minute mark.