Report to show future changes in status based on initial status

[Artm]

Hi all,

I need to make a report to show future changes in status based on initial status.

The table I have is:

Candidate Name              Review date       Status

John                                      1/1/2019              Ready in 1 year

Mark                                     4/1/2019              Ready in 6 months

Michael                                5/1/2019              Ready in 1 months

Peter                                     7/1/2019              Ready

I used table DATE:

Date = CALENDARAUTO()

and measure:

Monthly change =

VAR Maxdate = MAX('Date'[Date])

VAR Emptycount=

CALCULATE(

COUNTROWS(

CALCULATETABLE('Table','Table'[Review Date]<=Maxdate,ALL('Date'))),

(ISBLANK('Table'[Review Date]||'Table'[Review Date]>Maxdate)))

return

IF(ISBLANK(Emptycount),BLANK(),Emptycount)

to make the histogram:

But I want see on this histogram that Mark’s status will be changed to READY in October, 2019, as well as John’s and Michael’s statuses will be changed to READY in 2020 respectively.

Thanks everyone!!!

[sturlaws · Accepted solution]

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

[sturlaws · Accepted solution]

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

[Artm]

Thanks, that is exactly what i want.

I really appreciate your reply.

[sturlaws · Accepted solution]

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.

[Artm]

Everything works perfectly.

Many thanks once more.