Snapshot of logins over 6 month period

[goodbecenergy]

Hi and thanks in advance. 

A definition of an active user is one that has logged in to their account in the last 6 months. 

I am looking to create a chart with yyyy-mm on the x axis and on the y axis, the number of active users for the month & the previous 6 months rolled up in one figure. A user can log into their account more than once per month. 

So the data for August 2023 would be a count of the active users during March, April, May, June, July & August.

The columns that would be most useful probably are [Date], [Account ID] & [User ID] but there are plenty more besides.

I can work out whether a login has occured in the past 6 months from today but not sure how to get the rolling aspect. I have also tried countrows but that counts all logins rather than by account. 

I tried the rolling average quick measure and changed it to count rather than average but I couldn't get that to work either. 

Thank you.

[goodbecenergy · Accepted solution]

Thanks @whitch for the suggestion especially with the lack of data.

I have solved it by using DATESINPERIOD

bs_mat month login = CALCULATE(COUNTROWS(gateway),

DATESINPERIOD(gateway[date],MAX(dim_date[Date]),-6,MONTH))

[whitch]

This is difficult to anwer without knowing a couple more things about your data:

1. In addition to your logins data, do you also have a users table, which is just one row per user rather than one row per login? 

2. Do you have a date table, and if not, where is the x-axis of your chart coming from?