Hi, I’m reaching out to ask for advice/guidance around a dax measure solution to a question that has stumped me. I’m working on a Power BI dashboard that examines projects employees have worked on in a rolling 12-month period as of the most recent complete month (i.e., March 2025).
The first step is to establish the rolling 12-month period, which is the period starting from the most recently complete month (i.e., March 2025) and looks back 11 months. In this case that would be April 2024.
For each month in the rolling 12-month window, I need to count the number of distinct business units (BU) and directors that an employee has worked with and assign a “MonthlyPartnership” category. There are four categories, in ascending order:
1.No projects
2. 1 business unit and 1 director
3.1 business unit and multiple directors
4. Multiple business units and directors
The logic to assign a category is as follows:
- If an employee has not worked on a project within a given month (i.e., distinct count of BU and distinct count of director=0 or blank), then they are assigned to the “No projects” category.
- If an employee has worked on a project with 1 business unit and 1 director within a given month, then they’re assigned to that category, and so forth.
- If a BU or director appears for the first time in the current month (i.e., not present in any of the prior months in the window), that’s a signal of increased partnership exposure.
Once an employee hits a higher category, it should persist (i.e., carry forward) for all the following months in the rolling window, until they reach the highest category (i.e., Multiple business units and directors)
The attached PBIX file is an anonymized dataset with dummy data for one employee. I created several columns in the table: a) Month Year, b) MonthlyProjectCount, c) MonthlyBUCount, d) MonthlyDirectorCounts, e) MonthlyPartnership, and f) RollingCat_OnceHigherAlwaysHigher. The project, BU, and director counts are distinct counts for the month. The MonthlyPartnership, a base measure, is the assigned category for the month based on the distinct counts within the month. The RollingCat_OnceHigherAlwaysHigher is a cumulative column that shows how partnership categories change across the months.
All the columns except for column F are correctly functioning per the logic. However, Column F is not working as intended. For example, in October 2024, the employee in the Power BBIX partnered on project work with a new business unit not seen in April-September 2024, which should elevate the partnership category to “Multiple business units and directors” from October 2024 through March 2025.
In essence, the rolling measure should count the distinct counts of business units and directors AND also compare across all the months in the rolling 12-month window starting with the first month (in this case April 2024) through the current month in the row context of the visual, and assigning a category that is a cumulative analysis of all the categories so far. I tried different ways to get this rolling measure to work, but right now it is using a “higher water mark pattern” and not doing the additional comparative analysis I would like it to do.
Here’s the correct results that the measure should return.
| Correct Rolling Category | |
|---|---|
| Apr-24 | No projects in period |
| May-24 | No projects in period |
| Jun-24 | 1 Business unit and 1 Director |
| Jul-24 | 1 Business unit and 1 Director |
| Aug-24 | 1 Business unit and 1 Director |
| Sep-24 | 1 Business unit and 1 Director |
| Oct-24 | Multiple Business units and Multiple Directors |
| Nov-24 | Multiple Business units and Multiple Directors |
| Dec-24 | Multiple Business units and Multiple Directors |
| Jan-25 | Multiple Business units and Multiple Directors |
| Feb-25 | Multiple Business units and Multiple Directors |
| Mar-25 | Multiple Business units and Multiple Directors |
In summary, I need the measure to:
-
Determine the current month’s partnership category
-
Track the highest partnership category achieved in the rolling 12-month window up to the current month in the visual row context.
-
Compare current BUs and directors to those in previous months to detect any transitions to higher partnership categories.
-
Ensure that once the highest category is achieved, it is carried forward in subsequent months.
-
Once the month with the highest partnership category has dropped off the rolling twelve-month window, then it is possible for a partnership category to downgrade.
-
In the employee example, the downgrade would happen after October 2024 drops out of the rolling 12-month period.
-
Given that a 12-month rolling window means that each month, the oldest month drops off and the newest month is added, October 2024 would drop off the window after October 2025.
Therefore, the partnership category would be expected to downgrade in November 2025 when December 2024 becomes the new start of the rolling 12-month window, assuming no new months with ‘Multiple Business units and Multiple Directors’ are added, which would maintain the higher category.
I would greatly appreciate any advice/guidance with my dax code.
Thank you!!
EmployeeProjects.pbix (249.7 KB)
