Calculated Column that checks against prior month values for Y/N result

[sdcha2]

• I have tried to come up with a calculated column to do what I wanted but I haven't been able to.
  
  I have a table called TableA - In this table I have the following Columns
• Report Date - this is always the last date of each month
• Client Code - this is the client reference number (the client code is unique for each report date, as in if there is 12 months of data the client code will only be seen 12 months assuming they were a client for the full 12 months)
• Start Date - this is the date the client started
• Then there are an assortment of other data related columns that aren't relevant to my query.
  
  I want to create a calculated column that will check for a given Report Date and Client Code combination: if the client did not exist in the previous month return N
  if the client had a start date on or before the selected report date Return Y
  
  So effectively the column should show a result if there is also a row of data with data that meets the definition above or return N if there is no data on the client code in the previous report date month.
  
  

I have tried a few similar approaches and have had no luck with any of them. 

Does anyone know what I am doing wrong or have a better approach?

Column =
VAR CurrentRowReportDate = 'TableA'[Report Date]
VAR CurrentRowClientCode = 'TableA'[Client Code]
VAR PriorMonthReportDate = EOMONTH(CurrentRowReportDate, -1)
VAR PriorMonthData =
    CALCULATE(
        COUNTROWS('TableA'),
        'TableA'[Report Date] = PriorMonthReportDate,
        'TableA'[Client Code] = CurrentRowClientCode
    )
VAR PriorMonthStartDate =
    CALCULATE(
        MAX('TableA'[Start Date]),
        'TableA'[Report Date] = PriorMonthReportDate,
        'TableA'[Client Code] = CurrentRowClientCode
    )
RETURN
    IF(
        PriorMonthData = 0,
        "N",
        IF(
            PriorMonthStartDate > PriorMonthReportDate,
            "N",
            "Y"
        )
    )

[sdcha2 · Accepted solution]

I have came up with this solution incase and thought I would share in case it helps someone. Probably not the best approach but should do it.

_Existing Client =

    VAR CurrentClientCode = [Long Client Code]

    VAR CurrentReportDate = [Report Date]

    VAR EarlierInstances = CALCULATE(

            COUNTROWS('Table A'),

            FILTER(

                'Table A',

                [Long Client Code] = CurrentClientCode && [Report Date] < CurrentReportDate

            )

        )

    RETURN

        IF(EarlierInstances >=1,"Y","N")

[v-xuxinyi-msft]

Hi @sdcha2 

We are very happy to hear that you have solved the problem, you can mark your method as a solution so that people with the same problem can find it faster, and please feel free to let me know if you have any other questions.

Best Regards,
Yulia Xu