Forum Discussion
How to aggregate group by with time interval
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!
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
3 Replies
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
