Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a requirement where we need to plot the cummulative figures at a month level and the count at month level can increase/decrease because same users jump across statuses across time.
Sample Data:
User ID | Active/Inactive Flag | Last Modified Timestamp | Latest Record? |
CP123 | Active | 20-Jan-21 | No |
CP123 | Inactive | 21-Feb-22 | Yes |
CP124 | Active | 20-Jan-21 | No |
CP124 | Inactive | 5-Apr-22 | No |
CP124 | Active | 7-Apr-22 | Yes |
CP125 | Active | 6-Mar-21 | Yes |
CP126 | Active | 20-Apr-21 | Yes |
CP127 | Active | 4-Jun-21 | Yes |
CP128 | Active | 19-Jul-21 | Yes |
CP129 | Active | 2-Sep-21 | Yes |
CP130 | Active | 17-Oct-21 | No |
CP130 | Inactive | 1-Dec-21 | Yes |
CP131 | Inactive | 20-Jan-21 | Yes |
CP132 | Inactive | 20-Jan-21 | Yes |
CP133 | Inactive | 20-Jan-21 | Yes |
CP134 | Inactive | 20-Jan-21 | Yes |
CP135 | Inactive | 20-Jan-21 | Yes |
CP136 | Inactive | 7-Apr-22 | Yes |
CP137 | Inactive | 8-Apr-22 | Yes |
CP138 | Inactive | 9-Apr-22 | No |
CP138 | Active | 9-Jun-22 | Yes |
The ask is -
Chart to represent Active/Inactive Users count over time (Cummulative)
ex: How many Active/Inactive Users were present as of Jan-2022,Feb-2022 and so on...
Expected Output:
Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | |
Active Users Total - Cummulative | 7 | 6 | 6 | 6 | 6 | 7 |
Inactive Users Total - Cummulative | 6 | 7 | 7 | 10 | 10 | 9 |
Note: In above Output that values need to increase but it can also decrese when compared against the prevous month (Ex: Active User count have reduced in Feb month compared to Jan month )
How to implement this?
Thank you for the revert, but above is not what Im looking for.
In the output that im expecting, the count can also "decrease" when compared with the earlier month, in above output shared the count only keeps on increasing month after month which is not what im looking for
Example:
Lets consider we had 13 users (7 active and 6 inactive users) in total have as of end January.
Among the 7 active users was User A who was having the status "Active" as of end January. Hence this was user was counted against Active users count for the month of January
The same User A, lets assume that his status changed to "Inactive" as of end February and in Feb month no new users got added to the system. Hence the final count expected is still 13 users (6 active and 7 inactive users)
Note: Here the count of Active Users for Feb got reduced when compared against Jan and count of Inactive Users for Feb got increased when compared against Jan
Please let me know if the above explanation is not clear
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Users count active: =
VAR _userstablerecentdate =
SUMMARIZE (
ADDCOLUMNS (
SUMMARIZE (
User,
User[User ID],
User[Active/Inactive Flag],
User[Modified Timestamp]
),
"@latestdate",
CALCULATE (
MAXX (
FILTER ( User, User[Modified Timestamp] <= MAX ( 'Calendar'[Date] ) ),
User[Modified Timestamp]
)
)
),
User[User ID],
[@latestdate]
)
VAR _usercumulatetable =
CALCULATETABLE (
User,
TREATAS ( _userstablerecentdate, User[User ID], User[Modified Timestamp] )
)
RETURN
COUNTROWS (
FILTER ( _usercumulatetable, User[Active/Inactive Flag] = "Active" )
)
Users count inactive: =
VAR _userstablerecentdate =
SUMMARIZE (
ADDCOLUMNS (
SUMMARIZE (
User,
User[User ID],
User[Active/Inactive Flag],
User[Modified Timestamp]
),
"@latestdate",
CALCULATE (
MAXX (
FILTER ( User, User[Modified Timestamp] <= MAX ( 'Calendar'[Date] ) ),
User[Modified Timestamp]
)
)
),
User[User ID],
[@latestdate]
)
VAR _usercumulatetable =
CALCULATETABLE (
User,
TREATAS ( _userstablerecentdate, User[User ID], User[Modified Timestamp] )
)
RETURN
COUNTROWS (
FILTER ( _usercumulatetable, User[Active/Inactive Flag] = "Inactive" )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
90 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |