Report to show future changes in status based on initial status

Hi,

so, your requirement involves changing the value of the status-column when looking at a date which is past the review date + however long it takes to get ready. And, off course, you cannot change the value of a column. That leaves you with two options, adding a stand-alone table or adding new a new row when an item changes status.

For the stand-alone alternative, create a table containing all possible statuses, without any relationship to any other table. And add a new column in 'Table' to get a date for when the item changes status.

Then is time to create a measure which can handle the status change:

Monthly change = 
VAR Maxdate =
    MAX ( 'dimDate'[Date] )
VAR ready = "ready"
VAR ri1m = "Ready in 1 months"
VAR ri6m = "Ready in 6 months"
VAR ri1y = "Ready in 1 year"
VAR pastNewStatusDate =
    COUNTROWS (
        CALCULATETABLE (
            'Table';
            FILTER (
                ALL ( 'Table' );
                'Table'[Review date] < Maxdate
                    && Maxdate > 'Table'[New status date]
            )
        )
    )
RETURN
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( 'Status'[Status] ) = "Ready"; COUNTROWS (
            CALCULATETABLE (
                'Table';
                FILTER (
                    ALL ( 'Table' );
                    'Table'[Review date] <= Maxdate
                        && Maxdate <= 'Table'[New status date]
                        && 'Table'[Status] = "Ready"
                )
            )
        ) + pastNewStatusDate;
        SELECTEDVALUE ( 'Status'[Status] ) = ri1m; COUNTROWS (
            CALCULATETABLE (
                'Table';
                FILTER (
                    ALL ( 'Table' );
                    'Table'[Review date] <= Maxdate
                        && Maxdate <= 'Table'[New status date]
                        && 'Table'[Status] = ri1m
                )
            )
        );
        SELECTEDVALUE ( 'Status'[Status] ) = ri6m; COUNTROWS (
            CALCULATETABLE (
                'Table';
                FILTER (
                    ALL ( 'Table' );
                    'Table'[Review date] <= Maxdate
                        && Maxdate <= 'Table'[New status date]
                        && 'Table'[Status] = ri6m
                )
            )
        );
        SELECTEDVALUE ( 'Status'[Status] ) = ri1y; COUNTROWS (
            CALCULATETABLE (
                'Table';
                FILTER (
                    ALL ( 'Table' );
                    'Table'[Review date] <= Maxdate
                        && Maxdate <= 'Table'[New status date]
                        && 'Table'[Status] = ri1y
                )
            )
        );
        0
    )

As you can see, the code is a bit long. So I would say it works fine if you only have a handfull of statuses. If you have a lot of different statuses, I would try creating a new row for an item when the status changes.

Here is the pbix: Report to show future changes in status based on initial status.pbix

Report to show future changes in status based on initial status v2.pbix

excel file for mockup data

I have created a mockup dataset in order to test it for more data, and it seems to work fine. I have also modified slightly the original data you provided, in order to make it easier to validate the solution.

If you want to add more status changes, just add a column for each status change and change the measure accordingly.