How can I filter a table for the field values on the last(highest) date in a custom four week period, using DAX in Power BI?

Question

I created a calculated table based on start and end dates to check which manager is responsible for a product per date.

Result:

The financial department uses a four week period to report. So 13 periods per year. (not usable in a date table?)
Product number 8098 gets a different manager on the 23rd of januari this year. So at the start manager C5104 is responsible and at the end of period one (202301) C5107 is responsible. To get one responsible manager per period I'd like to filter the table in such a fashion that I get the numbers from the last date in a period. After filtering I don't need the [date] field anymore.

I hope I'm just missing something basic like the 'ELLEXCEPT' But I'm lost...

DISTINCT gives:

One row too many. I'd like to lose the row: 8098, C5104, 202301. To keep only rows showing the manager per product at the end of a period.

Answer 1

First, thank you for sharing the original structure; it immensely helped.

I have two suggested solutions using Power Query:

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

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

2. You need to create two referenced tables from your original table. Call them ProductManagerEndDate and ProductManagerStartEndDate

3. Disable load for ProductManagerEndDate and keep these columns only: Product, Manager, and EndDate Function Parameter, as shown in the image below.

4. 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 :).

5. The custom functions code for:

• Solution one:

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

• Solution two:

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.

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

7. Filter the column {Count the number of Projects} or whatever name you gave in step six to be less than 2.

8. 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 :)