How to aggregate group by with time interval

[ltang6]

Hello all,

I want to do some group by aggregation on my dataset. Not sure whether this should be solved with power query or DAX.

My dataset has 3 columns. The start date and end date represents the subscription period of the user, in the format mm/dd/yyyy. The first row stands for, user A is active from Jan 1, 2023 to Feb 1, 2023. User could be revoked from time to time. For example user A is revoked at Apr 15, 2023, and reactivated at Jul 1, 2023.

User	Start date	End date
A	01/01/2023	02/01/2023
A	02/01/2023	03/01/2023
A	03/01/2023	04/01/2023
A	04/01/2023	04/15/2023
A	07/01/2023	08/01/2023
A	08/01/2023	09/01/2023
B	01/01/2023	02/01/2023
B	02/01/2023	03/01/2023
B	03/01/2023	03/15/2023

What i need is to check for every individual day, how many users are active.

The output should be like this. I should plot this table with date as x-axis, and unique user count as y-axis.

Date	User
01/01/2023	A
01/02/2023	A
...	A
04/15/2023	A
07/01/2023	A
...	A
09/01/2023	A
01/01/2023	B
...	B
03/15/2023	B

Your help is very much appreciated, thank you very much!

[Ritaf1983 · Accepted solution]

Hi @ltang6 

Please refer to the linked tutorials:

PQ:

https://www.youtube.com/watch?v=ISDhR-TzwJk  

Dax

https://www.youtube.com/watch?v=YL7H1Rqckb0&t=128s

Please consider Accepting it as the solution to help the other members find it more quickly

[ltang6]

Didn't even know how to frame the problem...

This is exactly what i need. THANK YOU!

[Ritaf1983]

Glad to help ☺️