First, thank you for sharing the original structure; it immensely helped.
I have two suggested solutions using Power Query:
- Will remove all previous owners for a project if that project was handed over to another manager in the same period (which, according
to your data, is equal to Year Month combination) using Power Query.
- Will remove all previous owners for a project if that project was handed over to another manager.
After that, you can create your original calculation for your needed report.
I created test data on an excel sheet to use in my steps, as shown in the image below.
Steps to follow:
You need to add a column on your original table that will handle
the null values in the End date manager column by adding a custom column named EndDate Function Parameter; the formula is:
= if [End date manager] = null then DateTime.Date(DateTime.LocalNow()) else [End date manager]
You need to create two referenced tables from your original table. Call
them ProductManagerEndDate and ProductManagerStartEndDate
Disable load for ProductManagerEndDate and keep these columns
only: Product, Manager, and EndDate Function Parameter, as shown in
the image below.
For ProductManagerStartEndDate, keep these columns only: Product,
Manager, StartDate, EndDate, and EndDate Function Parameter, as
shown in the image below.
Please name the added column in step one and the table names in step 2 precisely what I wrote because there are used in the Custome function you will create next :).
- The custom functions code for:
Function name: Count the number of Handoverd Projects record in same period
let
Source = (product as number ,p_date as date) => let
Source = ProductManagerEndDate,
#"Filtered Rows" = Table.SelectRows(Source, each ([Product] = product)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.Year([EndDate Function Parameter]) = Date.Year(p_date) and
Date.Month([EndDate Function Parameter]) = Date.Month(p_date) and
Date.Day([EndDate Function Parameter]) >= Date.Day(p_date)
),
#"Row Count" = Table.RowCount(#"Filtered Rows1")
in
#"Row Count"
in
Source
Function name: Count the number of Handoverd Projects
let
Source = (product as number ,p_date as date) => let
Source = ProductManagerEndDate,
#"Filtered Rows" = Table.SelectRows(Source, each ([Product] = product)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [EndDate Function Parameter] >= p_date),
#"Row Count" = Table.RowCount(#"Filtered Rows1")
in
#"Row Count"
in
Source
If I understand your requirement correctly, you will go with solution one, so the following steps would depend on solution one. If you need the second solution, you only need to invoke the second solution Custome function instead of the first one.
Now, you need to go to the ProductManagerStartEndDate table, and from Add Column tab, you need to choose Invoke Custom Function and populate the data for the popped-out window, as shown in the image below.
Filter the column {Count the number of Projects} or whatever name you gave in step six to be less than 2.
Finally, you keep the Product, Manager, StartDate, and EndDate columns in the ProductManagerStartEndDate table then you can create your original calculation for your needed report.
I hope I helped in a way; if so, please mark this as an answer and vote for it :)
