cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vlad_M_
Frequent Visitor

Aggregate a calculated count for each day over a period

Hi

 

I'm working with a table a bit like this:

 

User Logins

IDTimestamp
121/09/2022 00:00:00
420/09/2022 00:00:00
120/09/2022 00:00:00
......

 

I have chosen to categorize a group of users as "Active Users", as the ones which has been logged in the past 14 days.

 

So, I can retrieve all my "Active Users" by using this Measure:

CALCULATE(DISTINCTCOUNT('User Logins'[ID]), 'User Logins'[Timestamp] > TODAY()-14)

 

But - here's my problem. I want to create a graph of "Active Users" for the past 7 days.

 

So I need to somehow replace "TODAY()" with "Whatever X-day is showed on the x-axis on the graph".

 

Just to make it more clear, maybe we have the following results of the count:

Count of unique ID'sDate
1021/09/2022
1520/09/2022
519/09/2022
7

18/...

417/...
716/...

 

Then I would like to get the following result (just displayed in a graph instead):

Count of unique ID's the past 14 daysDate
82 (10+15+5+7+4+7+...)21/09/2022
89 (15+5+7+4+7+...)20/09/2022
75 (5+7+4+7+...)19/09/2022
78 (7+4+7+...)18/09/2022

 

I hope it makes sense and that someone knows the necessary formula! 🙂

 

/Vlad

1 ACCEPTED SOLUTION
Vlad_M_
Frequent Visitor

For others - I've found my own solution for this.

 

I created a new table with the following settings:

 

Active Users Past 7 Days = FILTER(Dates, Dates[Date] > Today() - 7 && Dates[Date] <= Today())

 

 

 

Where the table "Dates" is a calendar table. So it just gives me a column with the past 7 days.
 
Then I added the following column to the table:
 

Active Users =
VAR Selected = 'Active Users Past 7 Days'[Date]
RETURN
CALCULATE(DISTINCTCOUNT('User Logins'[UserId]), 'User Logins'[timestamp] > Selected-14)

 

This results in a table with two columns. One being the past 7 days, another being the sum og distinct user id's for the past 14 days per day.

View solution in original post

3 REPLIES 3
Vlad_M_
Frequent Visitor

For others - I've found my own solution for this.

 

I created a new table with the following settings:

 

Active Users Past 7 Days = FILTER(Dates, Dates[Date] > Today() - 7 && Dates[Date] <= Today())

 

 

 

Where the table "Dates" is a calendar table. So it just gives me a column with the past 7 days.
 
Then I added the following column to the table:
 

Active Users =
VAR Selected = 'Active Users Past 7 Days'[Date]
RETURN
CALCULATE(DISTINCTCOUNT('User Logins'[UserId]), 'User Logins'[timestamp] > Selected-14)

 

This results in a table with two columns. One being the past 7 days, another being the sum og distinct user id's for the past 14 days per day.

Greg_Deckler
Super User
Super User

@Vlad_M_ Maybe:

 

Measure =
  VAR __Date = MAX('Table'[Date]) //whatever is used in the x-axis
  VAR __MinDate = __Date - 14
RETURN
  SUMX(FILTER(ALL('Table'),[Date] >= __MinDate && [Date] <= __Date),[Count of Unique ID's])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hey Greg

For some reason I couldn't get it to work the way I hoped, but thanks for the input 🙂

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors