Get overview of shifted project end dates

[DocDri]

Hello,

I have the following challenge, where I need some help:

I have a table with project data (values, end-date) for different reporting periods like this

project | period | valueX | value Y | end-date (YYYY.MM)
A | 2024.12 | 10 | 20 | 2025.01
A | 2025.01 | 15 | 27 | 2025.01
A | 2025.02 | 14 | 27 | 2025.01
A | 2025.03 | 15 | 20 | 2025.09
B | 2024.12 | 40 | 10 | 2025.07
B | 2025.01 | 40 | 10 | 2025.07
B | 2025.02 | 50 | 10 | 2025.09
B | 2025.03 | 20 | 10 | 2025.01
C | 2024.12 | 80 | 12 | 2025.01
C | 2025.01 | 85 | 12 | 2025.03
C | 2025.02 | 86 | 12 | 2025.05
C | 2025.03 | 87 | 12 | 2025.08

I need an overview of all projects which had an end-date=2025.01 in period=2024.12 and if they have another end-date (if yes, which one) in the current period (could be 2025.1,2 or 3 in the example above).

Example: for current period = 2025.02 it should deliver the following table:

project | valueX | value Y | end-date old | end-date new
A | 10 | 20 | 2025.01 | null (no change of end-date)
C | 80 | 12 | 2025.01 | 2025.05

Many thanks for any help!

[Anonymous · Accepted solution]

Hi @DocDri 

Thank you very much bhanu_gautam for your prompt reply.

For your question, here is the method I provided:

Create a new table.

OverviewTable = 
VAR CurrentPeriod = DATE(2025, 2, 1)
RETURN
FILTER(
    ADDCOLUMNS(
        FILTER(
            'Table',
            'Table'[period] = DATE(2024, 12, 1) && 'Table'[end-date] = DATE(2025, 1, 1)
        ),
        "end-date new", 
        IF(
            CALCULATE(
                MAX('Table'[end-date]),
                FILTER(
                    'Table',
                    'Table'[project] = EARLIER('Table'[project]) && 
                    'Table'[period] = CurrentPeriod
                )
            ) = DATE(2025, 1, 1),
            BLANK(),
            CALCULATE(
                MAX('Table'[end-date]),
                FILTER(
                    'Table',
                    'Table'[project] = EARLIER('Table'[project]) && 
                    'Table'[period] = CurrentPeriod
                )
            )
        )
    ),
    [end-date new] <> BLANK() || [end-date] = DATE(2025, 1, 1)
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@DocDri , First create a intial table

Create a new table that filters the data for the initial period (2024.12) with end-date = 2025.01.

DAX
InitialPeriodTable =
FILTER(
YourTable,
YourTable[period] = "2024.12" && YourTable[end-date] = "2025.01"
)

Merge the InitialPeriodTable with the original table to get the current period data.

DAX
MergedTable =
SELECTCOLUMNS(
ADDCOLUMNS(
InitialPeriodTable,
"end-date new",
LOOKUPVALUE(
YourTable[end-date],
YourTable[project], InitialPeriodTable[project],
YourTable[period], "2025.02"
)
),
"project", [project],
"valueX", [valueX],
"value Y", [value Y],
"end-date old", [end-date],
"end-date new", [end-date new]
)

Create the final table to display the required columns.

DAX
FinalTable =
SELECTCOLUMNS(
MergedTable,
"project", [project],
"valueX", [valueX],
"value Y", [value Y],
"end-date old", [end-date old],
"end-date new", IF([end-date new] = BLANK(), "null", [end-date new])
)