Repeat ID within a Rolling 12 month calculated Column

[Will_Powell93]

Hello All, 

Unfortunately, I am unable to share my data for security reasons. 

I am looking to create 2 columns depending on Category 1 or 2 equalling YES, it will tell me if the customer ID has appeared before within the last 12 months and if so how many times.

an example of my data is below

Row ID	Date	customer ID	Category 1	Category 2
1	01/01/2022	1	YES	NO
2	01/01/2022	2	YES	NO
3	01/02/2022	1	NO	YES
4	01/03/2022	1	YES	YES

any help would be great, 

THANKS ALL

[Will_Powell93]

@v-rongtiep-msft thank you for your reply.

Unfortunately, this is not what I am looking for. 

I am looking to replace "Repeat CAT1" & "Repeat CAT2" with calculated columns. These were created as an example of what outcome I am expecting/ would like.

The calculation should first identify if the customer ID has been repeated within the last 12 months (from today, not the date of the row) with category 1 as YES. If the above is true count row id with that person id, within the last 12 months. 

This would then be duplicated on another column to identify the same but with category 2 as YES.

I hope this makes sense, 

Thanks 

Will 

[v-rongtiep-msft]

Hi @Will_Powell93 ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

Measure =
VAR _12months =
    EDATE ( MAX ( 'Table'[Date] ), -12 )
VAR _customer =
    SELECTEDVALUE ( 'Table'[Customer ID] )
VAR _1re =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer ID] = SELECTEDVALUE ( 'Table'[Customer ID] )
                && ( 'Table'[Category 1] = "Yes"
                || 'Table'[Category 2] = "Yes" )
                && 'Table'[Date] < SELECTEDVALUE ( 'Table'[Date] )
                && 'Table'[Date] >= _12months
        )
    )
RETURN
    _1re

Measure2 =
COUNTAX ( FILTER ( ALL ( 'Table' ), [Measure] <> BLANK () ), [Measure] )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Will_Powell93]

Apologies, 

Ideally 2 new colunms would be added to the orginal dataset, to show count of cases that the customer has appeared in example below. 

Row ID	Date	Customer ID	Category 1	Category 2	Repeat CAT1	Repeat CAT2
1	01/01/2020	1	YES	NO
2	01/01/2022	2	YES	NO	1
3	01/02/2022	1	NO	YES	2	2
4	01/03/2022	1	YES	YES	2	2
5	01/03/2019	3	NO	YES
6	01/10/2022	3	YES	NO	1
7	01/10/2022	4	YES	NO	2
8	01/10/2022	4	YES	NO	2

I attempted the below calculation but return incorrect numbers.

VAR ID=customerID
RETURN
IF( CAT1 = "YES" && DATE > (TODAY() - 365),

Calculate( count(rowid)

,filter(table , CAT1 = "YES" && DATE > (TODAY() - 365) && ID=CustomerID))

,blank())

@FreemanZ hope this helps, 

thanks 

[FreemanZ]

hi @Will_Powell93 

the logic is too vague. additional explanation with expected table would be very helpful for those who may help you.