Rolling 12 month unique count in line graph

[ogonzalez1]

Has anyone done a 12 month rolling count distinct  but for every month? I have a column that has the first day of the month ex: 

User A| 3/1/2018
User B| 2/1/2018
User A| 1/1/2019
User C| 3/1/2019

If I were do do a  rolling 12 month unique count as of 3/1/2019, my result would be 3 users. However, I also want to have the count as of 2/1/2019 = 2.  In the end, on point  for each month in the line graph, it would be showing the 12 month distinct count.   Feb=2 Mar=3 and so on. Is there any set analysis that could be leveraged for this scenario? I tried: 

=Count({$<[Date]= {">=$(=(addmonths(LatestDate,-12)))"}>} Distinct UserID)

But this only gives me the unique count for the most recent date. I also tried :

If(Date=LatestDate, Count({$<[Date]= {">=$(=(addmonths(LatestDate,-12)))"}>} Distinct UserID))

I thought if I managed to get this, I could just add multiple nested IFs for each month (LatestDate-1),(LatestDate-2) and so on but this also failed. 

Any help would be greatly appreciated!

[sunny_talwar · Accepted solution]

Do you have month as your dimension? If you do, the best way to do this would be to use the The As Of Table

[pradosh_thakur]

What is LatestDate ?

try this may be 

=Count({$<[Date]= {">=$(=(addmonths(today(),-12)))"}>} Distinct UserID)

the above expression doesn't have a upper limit

[ogonzalez1]

Thanks for your help!

Would this expression only return the current unique count though? I guess what I'm looking for is for the unique count to be calculated for each month on the line graph.  For example:

The point for Feb 2019 would have a 12 month  rolling unique count (Feb 2018-Feb 2019), the point for Mar 2019 would have the 12 month rolling unique count (Mar 2018-Mar 2019) and so on.

[ogonzalez1]

Yes I currently have month as a dimension. Of note, a user could have many records since they can be on the system many times.

[sunny_talwar]

I def. think you need the as of table to do this, unless you will be making this unnecessarily complicated for yourself

[ogonzalez1]

I think the As of table definitely helped! The only issue I have now is that the current date filter I was using will not apply to the graph that is using the as of date. Have you experienced this or gotten around this somehow?

[sunny_talwar · Accepted solution]

You can apply current date filter to as of date using set analysis

{<[AsOfDate] = p(Date)>}

[ogonzalez1]

This is working so far!