counting date instances occurring for last 2 months by user

[domdom]

Hello,

I have a question if you are able to help with it.  I have the following table of dates against user_ids (these will be updating frequently about 20,000 records):

TABLE A:

----------------------

date                      user_id

20/02/2016           A

22/02/2016           C

27/02/2016           D

04/03/2016           C

06/03/2016           C

01/04/2016           C

01/04/2016           A

30/04/2016           B

05/05/2016           C

06/05/2016           B

26/05/2016           A

01/06/2016           B

01/09/2016           C

I'd like to add another column to this same table which counts the total dates that exist between the date of the current record less 2 months for each user_id.  E.g.  for the value against the date 05/05/2016 for user_id C - it should count the total dates in the table between 05/03/2016 to 05/05/2016 that also have a user_id of C- so it would show 3 - as the following dates fall within this range:

06/03/2016           C

01/04/2016           C

05/05/2016           C

hope that makes senses?

Using my sample data - the final table should look as follows:

TABLE A:

----------------------

date                      user_id          total dates less 2 months by user

20/02/2016           A                   1

22/02/2016           C                   1

27/02/2016           D                   1 

04/03/2016           C                   2 

06/03/2016           C                   3

01/04/2016           C                   4

01/04/2016           A                   2

30/04/2016           B                   1

05/05/2016           C                   3

06/05/2016           B                   2

26/05/2016           A                   2

01/06/2016           B                   3

01/09/2016           C                   1  

If you are able to help with this it would be most appreciated!

many thanks

[v-jiascu-msft]

Hi @domdom,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Zubair_Muhammad · Accepted solution]

@domdom

Hi,

Please try this column

Column =
VAR CurrentDate = Table1[date ]
VAR TwoMonthsbefore =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 2, DAY ( CurrentDate ) )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[user_id ] ),
            Table1[date ] <= CurrentDate
                && Table1[date ] >= TwoMonthsbefore
        )
    )

[Zubair_Muhammad]

@domdom

pbix file attached as well