Forum Discussion

sviswanathan's avatar
sviswanathan
Regular Visitor
2 years ago
Solved

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

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

AccountNumTimePeriodProductNameColumn Result
123451-Nov-23HikeLegacy
123447-Mar-23CycleLegacy
145675-May-24CarLegacy
178901-Dec-21TruckLegacy
1234511-Jun-24HikeLegacy
1897015-Nov-23AutoLegacy
1789022-Apr-24TrainNew
1234520-Jul-24TrainNew

 

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

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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