How to see if ID is in previous month when all months are in one table

[Anonymous]

Below is my data, the second column is the unique idenfier I am trying to create a formula each month to say new/existing. If it was in previous month it is existing if it wasn't its new. I have tried so many ways and just can't get it. Right now I only have Feb/Mar data loaded. Thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below:

New/Existing =
VAR _curperiod =
    SELECTEDVALUE ( 'SBU'[Report Period] )
VAR _curid =
    SELECTEDVALUE ( 'SBU'[ID] )
VAR _count =
    CALCULATE (
        COUNT ( 'SBU'[ID] ),
        FILTER (
            ALL ( 'SBU' ),
            'SBU'[ID] = _curid
                && 'SBU'[Report Period] <= _curperiod
        )
    )
RETURN
    IF ( _count > 1, "Existing", "New" )

If the above one is not working for your scenario, please provide your expected result with sample or screenshot. Thank you.

Best Regards

[-Bri]

I get this to work in a measure. Can it be created in a column?

[Anonymous]

Hello, all data is in one table?

[Jihwan_Kim]

Hi, @Anonymous 

Do you have custom-date-table ?

[Anonymous]

Yes, I have a standardard date table

[Jihwan_Kim]

Hi, @Anonymous 

Then you can create one measure as I mentioned above.

All data come from the date table, and your main table.

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understand your question.

Inside your measure, please try to define the below by using VAR.

first table: VALUES(customersID-culumn)  as current month customer, and 

second table: calculatetable (VALUES(customersID-culumn), filter(alldatestable, monthnumber = maxmonthnumber-1)) as previous month customer.

intersect (first table, second table) = existing customers table

except (first table, second table) = new customer table

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.