Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a set of user data from AD, with no access to source HR data (which is spread across 48 different HR systems). My goal is to use a line chart and enable the forecast options to try and at least roughly forecast growth over the next three years, using the prior 5 as a reference. Technically I guess my goal is to generate a forecast, though it would also be good to show on a line chart the general flow of adds/removes that looks realistic.
While I don't have actual term data, the company generally hasn't removed many users from AD in the last 4-5 years, only disabled them. Currently, I'm making the logical assumption that if an account is disabled, then the last date of modification is the same as the term date. From a manual perspective, I've created measures to manually calculate users added/removed for each year, along with more measures to calculate the running total by working backwards (Current Total - This Year Adds + This Year Removes = Prior Year Total, then repeat process for each preceeding year using output of prior sequence). The issue is that this is both static and dependent on measures that won't translate to the line chart visual, based on my attempts thus far. I'm sure there has to be a better way, maybe using a summary table or something, but my attempts thus far have been unsuccessful. Supplemental details as follows:
Example Data (scrubbed approximation - I have 33k rows in real data): Table Name = AllUsers
Name | sAMAccountName | Enabled | whenCreated | whenChanged |
Jeffrey Schroedinger | schjef01 | TRUE | 1/3/2008 14:47 | 7/31/2023 13:02 |
Ashley Brown | ashbro03 | TRUE | 4/10/2018 13:35 | 10/24/2023 16:14 |
Mathew Duncan | madun4 | TRUE | 10/5/2020 11:30 | 7/31/2023 15:36 |
Laura Berg | laube3 | FALSE | 6/30/2004 21:15 | 6/22/2022 16:30 |
Sean Bean | sebea9 | FALSE | 3/8/2019 12:21 | 7/20/2023 6:36 |
I do also have a dedicated generated date table called 'DateTable' that has various columns for things like Year, Quarter, Month, etc. There is a relationship between the Date column of the DateTable and the 'whenCreated' column in my AllUsers table.
From a manual perspective, I've tested the following measures and results:
The closest I've gotten so far to any sort of meaningful approach is the following:
This somewhat works, but the numbers reflected in the visual don't line up with my manual efforts with the individual measures or my on paper checks for prior years numbers. My assumption is that this is because I am only showing total object counts and not reflecting disabled objects as removes. Adding a filter to new objects to only show Enabled ones naturally adjusts the numbers some, but doesn't seem to fix the issue, so the only thing I can think to do is to maybe figure out how to do a summary table with the running totals, or maybe add all my individual measure calculations into a single measure? I know I'm probably making this harder than it needs to be, but I'm determined to improve my DAX skills, so any help would be greatly appreciated.
It looks like you're on the right track with your DAX measures and analysis. To create a dynamic forecast and visualize the user growth over the next three years, you can follow these steps:
Total Users = CALCULATE(COUNTROWS(AllUsers), ALL(AllUsers))
Added This Year = CALCULATE(COUNTROWS(AllUsers), FILTER(AllUsers, YEAR(AllUsers[whenCreated]) = MAX(DateTable[Year])))
Removed This Year = CALCULATE(COUNTROWS(AllUsers), FILTER(AllUsers, YEAR(AllUsers[whenChanged]) = MAX(DateTable[Year]) && NOT AllUsers[Enabled]))
Create Running Total Measures: Now, create measures to calculate the running total by working backward from the current year.
Total Users Running Total =
CALCULATE(
[Total Users],
FILTER(
ALL(DateTable),
DateTable[Year] <= MAX(DateTable[Year])
)
)
Added Running Total =
CALCULATE(
[Added This Year],
FILTER(
ALL(DateTable),
DateTable[Year] <= MAX(DateTable[Year])
)
)
Removed Running Total =
CALCULATE(
[Removed This Year],
FILTER(
ALL(DateTable),
DateTable[Year] <= MAX(DateTable[Year])
)
)
Create Forecast Measures: To create a forecast, you can use a combination of the base measures and running total measures. Adjust the logic based on your requirements.
Forecast Users =
CALCULATE(
[Total Users Running Total] - [Added Running Total] + [Removed Running Total],
FILTER(
ALL(DateTable),
DateTable[Year] > MAX(DateTable[Year]) && DateTable[Year] <= MAX(DateTable[Year]) + 3
)
)
Ensure that your relationships between the DateTable and AllUsers table are correctly established.
Note: Adjust the measures based on your specific requirements and data model. These measures are examples, and you might need to adapt them to fit your exact data structure and business logic.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
First off, I would like to offer a very sincere thank you for replying in such detail and offering your help. Unfortunately, while I believe your comments did solve some of the challenge, I'm afraid something is still off as the numbers aren't coming out right.
When I created all of the measures as shown, I ended up with a flat line showing the maximum for both the total and the forecast users measures. I managed to fix that by using 'MIN' instead of 'MAX' in some of the forumlas, but the numbers for running total still aren't coming out. The running total and the calculated total for each year both show up exactly the same when placed on a line chart together, though now they do reflect the total users in each year, but I also appear to have a flat line at the end now from 2024 to 2026 for some reason.
I've been forced to perform a refresh on the AD data due to a PBI crash, so my numbers have changed slightly, but I'll try to walk through what I'm seeing, vs what I expect to see.
2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 |
11699 | 14573 | 16154 | 18416 | 22551 | 26925 | 26925 | 26925 | 26925 |
2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 |
1599 | 1885 | 2021 | 2193 | 2520 | 3212 | 3212 | 3212 | 3212 |
2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
5066 | 5066 | 5066 | 5066 | 5066 | 5066 |
Any time that I try to dynamically do the calculations, I get weird results, but if I do the work in static steps, I get the correct results. At the end of the day, I am trying to achieve the following:
I currently have the following measures defined:
I really wish I could share the data set/file, but it would take a substantial amount of time to scrub it sufficiently that I wouldn't cause an incident...like my consulting company being fined by our customer and me losing my job. That said, the min-sample should be able to be extrapolated to provide a working set...shouldn't need more than a few dozen entries for each year, with a random subset disabled, just not sure how to create that quickly. Just need enough to figure out the mechanism right?
Thank you for the detailed information. Let's try to refine the measures to get the correct running totals. Based on your feedback, I'll suggest modifications to your existing measures:
All Users = COUNTROWS(AllUsers)
Users Added This Year = CALCULATE(
COUNTROWS(AllUsers),
FILTER(AllUsers, YEAR(AllUsers[whenCreated]) = 2023)
)
Users Removed This Year = CALCULATE(
COUNTROWS(AllUsers),
FILTER(AllUsers, YEAR(AllUsers[whenChanged]) = 2023 && NOT AllUsers[Enabled])
)
Users Running Total = CALCULATE(
[All Users],
FILTER(ALL(DateTable), DateTable[Year] <= MAX(DateTable[Year]))
)
Users Added Running Total = CALCULATE(
[Users Added This Year],
FILTER(ALL(DateTable), DateTable[Year] >= MIN(DateTable[Year]))
)
Users Removed Running Total = CALCULATE(
[Users Removed This Year],
FILTER(ALL(DateTable), DateTable[Year] <= MAX(DateTable[Year]))
)
Forecast Users = CALCULATE(
[All Users] - [Users Added Running Total] + [Users Removed Running Total],
FILTER(ALL(DateTable), DateTable[Year] > MAX(DateTable[Year]))
)
A few notes on the changes:
Removed ALL(AllUsers): It's not necessary to use ALL(AllUsers) in the [All Users] measure as it already counts all rows in the AllUsers table.
Removed Min and Flipped Operator: I noticed that you changed some of the operators in your measures, but I reverted them back to the original state. This may not always be necessary, and it depends on the specific context of your data. Ensure that the operators are correct for your data structure.
Removed '+3' in Forecast Users: Since you're using the MAX(DateTable[Year]) in the forecast calculation, there's no need to add '+3'. The filter condition already takes care of projecting into the future.
After updating the measures, create a line chart with the 'Year' from DateTable on the x-axis and use measures like [Users Running Total], [Users Added Running Total], and [Users Removed Running Total] for the y-axis to visualize the running totals.
If the issue persists, it might be beneficial to break down the problem into smaller steps. You can create intermediate measures to check the values at each step to identify where the discrepancy might be occurring. For example, you could create a measure for each year to check the running totals independently for each year. This way, you can pinpoint which step might be causing the unexpected results.
If you encounter specific issues with individual steps or measures, feel free to share them, and I'll do my best to assist you further.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |