Work backwards from total to determine annual totals

[Merddyn]

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:

• Total Users = Calculate(CountRows(AllUsers), RemoveFilters())
  • Value: 33737
• Added This Year = Calculate(CountRows(AllUsers), Filter(AllUsers, AllUsers[whenCreated].[Year] = 2023))
  • Value: 4942
• Removed This Year = Calculate(CountRows(AllUsers), Filter(AllUsers, AllUsers[whenChanged].[Year] = 2023 && NOT ALLUsers[Enabled])) 
  • Value: 3073
• Total Users 2022 =  [Total Users] - [Added This Year] + [Removed This Year]
  • Value: 25722
• Users Created 2022 = Calculate(CountRows(AllUsers), Filter(AllUsers, AllUsers[whenCreated].[Year] = 2022))
  • Value: 4686
• Users Removed 2022 = Calculate(CountRows(AllUsers), Filter(AllUsers, AllUsers[whenChanged].[Year] = 2022 && NOT ALLUsers[Enabled])) 
  • Value: 1184
• Total Users 2021 = [Total Users 2022] - [Users Created 2022] + [Users Removed 2022]
  • Value: 22220
• <Repeat for years back to 2018>

The closest I've gotten so far to any sort of meaningful approach is the following:

• Measure (got this from some other post as I'm still ramping up on DAX)
  • [New Objects] = Calculate(CountRows(AllUsers), Filter(All(DateTable[Year]), DateTable[Year] < Min( DateTable[Year])))
• Line chart visual
  • X-axis = DateTable[Year]
  • Y-axis = [New Objects]
  • Futher Analysis -> Forecast
    • On
    • Units = Points
    • Forecast length = 3
    • Defaults for remaining settings

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.

[123abc]

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:

1. Create Base Measures: Start by creating base measures for the total users, users added, and users removed for each year. You've already done this, but it's essential to make sure these measures are accurate.

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

1. Visualize in Line Chart: Now, create a line chart with DateTable[Year] on the X-axis and [Total Users Running Total], [Added Running Total], [Removed Running Total], and [Forecast Users] on the Y-axis.

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.

[Merddyn]

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. 

• Total User Objects 33841
  • Enabled 26925
  • Disabled 6916 (they might have done some cleanup today, which will mess things up some, but still want to figure this out)
• If I put the [Users Running Total] or [Forecast Users] measures into a table, with Year for columns and the measure for values, I see this:

2018	2019	2020	2021	2022	2023	2024	2025	2026
11699	14573	16154	18416	22551	26925	26925	26925	26925

• Unclear where above totals are coming from since, using the running values below, I do not get the values shown for 2022 or earlier (e.g. 26925 - 5061 + 3212 does not equal 22551)
• If I put [Users Removed Running Total] and Year into a table, I get this:

2018	2019	2020	2021	2022	2023	2024	2025	2026
1599	1885	2021	2193	2520	3212	3212	3212	3212

• If I put [Users Added Running Total] and Year into a table, I see this:

2018	2019	2020	2021	2022	2023
5066	5066	5066	5066	5066	5066

• If I put [Users Added This Year] or [Users Removed This Year] into Card visuals, I see 5066 and 3212 respectively, which is accurate and aligns with the table
• If I create two new measures for [Users Added 2022] and [Users Removed 2022] (same as the 'This Year', but hard coded year value) and put them into Card visuals, I see 4680 and 1177 respectively
  • Does not match either of the running total tables

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:

• A single measure that can be dropped into a Line chart visual
• Resulting line will show the correct count of users for each year based on the following math
  • User Total for X Year - Users Created in X Year + Users Removed in X Year

I currently have the following measures defined:

• All Users = Calculate(Countrows(AllUsers), ALL(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])))
  • Changed Max to Min and flipped operator because when viewed as a table it only showed years 2023, 2024, 2025 and 2026 (still all same value), and Line Chart just shows a flat line, but flipping other way shows prior years and better line
• 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]) && Max(DateTable[Year])))
  • Removed the '+3' because it initially just created a flat line at the end and, after the Min switch, having it or not made no difference in the chart

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?

[123abc]

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:

1. 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.

2. 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.

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