Match data values from same column within a specific time period and cross validate it with another,

[sviswanathan]

Hi,

I have 3 Columns AccountNumber, TimePeriod, ProductName

Use Case: Match value in AccountNumber and ProductName within the same AccountNumber and ProductName column within TimePeriod between this year and pervious year. If both AccountNumber and ProductName in the same row matches with the same AccountNumber and ProductName in another row then the associated column should display as Legacy else New.

Sample Date

AccountNum	TimePeriod	ProductName	Column Result
12345	1-Nov-23	Hike	Legacy
12344	7-Mar-23	Cycle	Legacy
14567	5-May-24	Car	Legacy
17890	1-Dec-21	Truck	Legacy
12345	11-Jun-24	Hike	Legacy
18970	15-Nov-23	Auto	Legacy
17890	22-Apr-24	Train	New
12345	20-Jul-24	Train	New

I have tried many times in DAX but not able to get hte right combination.

[sviswanathan]

Perfecto! Thanks worked like a charm. @rajendraongole1 

[rajendraongole1 · Accepted solution]

Hi @sviswanathan  Can you try below calculated column replace table name with your model table

currently i have used legne

Column Result_R =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentYearStart = DATE(CurrentYear, 1, 1)
VAR PreviousYearStart = DATE(CurrentYear - 1, 1, 1)
VAR CurrentYearEnd = DATE(CurrentYear, 12, 31)
VAR PreviousYearEnd = DATE(CurrentYear - 1, 12, 31)

VAR IsCurrentOrPreviousYear =
'Legne'[TimePeriod] >= PreviousYearStart &&
'Legne'[TimePeriod] <= CurrentYearEnd

VAR IsLegacy =
CALCULATE(
COUNTROWS('Legne'),
FILTER(
'Legne',
'Legne'[AccountNum] = EARLIER('Legne'[AccountNum]) &&
'Legne'[ProductName] = EARLIER('Legne'[ProductName]) &&
'Legne'[TimePeriod] < CurrentYearStart &&
'Legne'[TimePeriod] >= PreviousYearStart
)
) > 0

RETURN
IF(
IsCurrentOrPreviousYear &&
IsLegacy,
"Legacy",
"New"
)

getting result:

Hope this helps