Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Snapshot of logins over 6 month period

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.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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))

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Resolver I
Resolver I

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? 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.