cancel
Showing results for
Did you mean:
Frequent Visitor

Aggregate a calculated count for each day over a period

Hi

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

 ID Timestamp 1 21/09/2022 00:00:00 4 20/09/2022 00:00:00 1 20/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:

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's Date 10 21/09/2022 15 20/09/2022 5 19/09/2022 7 18/... 4 17/... 7 16/...

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

 Count of unique ID's the past 14 days Date 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! 🙂

1 ACCEPTED SOLUTION
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

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.

3 REPLIES 3
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

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hey Greg

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

Announcements