Forum Discussion

Will_Powell93's avatar
Will_Powell93
Frequent Visitor
3 years ago

Repeat ID within a Rolling 12 month calculated Column

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 IDDatecustomer IDCategory 1Category 2
101/01/20221YESNO
201/01/20222YESNO
301/02/20221NOYES
401/03/20221YESYES

 

 

any help would be great, 

THANKS ALL

Rolling 12 Months
rolling sum

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Will_Powell93 

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

  • Will_Powell93's avatar
    Will_Powell93
    Frequent Visitor
    3 years ago

    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 IDDateCustomer IDCategory 1Category 2Repeat CAT1Repeat CAT2
    101/01/20201YESNO  
    201/01/20222YESNO1 
    301/02/20221NOYES22
    401/03/20221YESYES22
    501/03/20193NOYES  
    601/10/20223YESNO1 
    701/10/20224YESNO2 
    801/10/20224YESNO2 

     

    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    Will_Powell93
    Frequent Visitor
    3 years ago

    Anonymous 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