Changes in Date MoM

[GJUDGE]

Hi,

I have a set of projects where the project status and key dates is reported each month. I am trying to write a measure that will quickly show the projects who have had date changes between the latest month reporting period and the one previously. In the example below, project A has had a change in construciton date moving from Aug-Sept and project C has had a change in commissioning date.

Will I need to write a measure for each Date that I am interested in? I'm also keen that this can  be dynmaic as possible so that when a new month is added the measure still compares the latest reporting month to the previous month.

Thanks in advance!

Project	Output	Construction Start Date	Commissioning Date	Reporting Period
A	50	01/01/2023	01/06/2023	Aug-21
B	25	01/04/2023	07/04/2023	Aug-21
C	50	01/05/2023	01/07/2023	Aug-21
A	50	01/02/2023	01/06/2023	Sep-21
B	25	01/04/2023	07/04/2023	Sep-21
C	50	01/05/2023	01/06/2023	Sep-21

[MFelix]

Hi @GJUDGE ,

I was thinnking about the issue and you can use this measure to reflect what is changing in a single measure:

Has Date Changes = 
VAR TEmpTable =
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[Reporting Period] <= MAX ( 'Calendar'[Date] )
                && 'Table'[Reporting Period]
                    >= DATE ( YEAR ( MIN ( 'Calendar'[Date] ) - 1 ), MONTH ( MIN ( 'Calendar'[Date] ) - 1 ), 1 )
        ),
        'Table'[Project],
        "Commissioning", DISTINCTCOUNT ( 'Table'[Commissioning Date] ),
        "Starting Date", DISTINCTCOUNT ( 'Table'[Construction Start Date] )
    )
VAR Comissiningdate =
    IF (
        COUNTROWS ( FILTER ( TEmpTable, [Commissioning] > 1 ) ) > 0,
        "Change in commission date"
    )
VAR StartDate =
    IF (
        COUNTROWS ( FILTER ( TEmpTable, [Starting Date] > 1 ) ) > 0,
        "Change in Starting date"
    )
RETURN
    Comissiningdate
        & IF (
            OR ( ISBLANK ( Comissiningdate ), ISBLANK ( StartDate ) ),
            BLANK (),
            " | "
        ) & StartDate

[v-zhangti]

Hi, GJUDGE

You can try the following methods:

1. I wrote two  measures for these two dates. Please understand that CSD changes represents construction start date changes and CD changes represents commissioning date changes.

CSD changes =
IF (
    CALCULATE (
        MIN ( 'Table'[Construction Start Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Project] = SELECTEDVALUE ( 'Table'[Project] )
        )
    )
        = SELECTEDVALUE ( 'Table'[Construction Start Date] ),
    BLANK (),
    SELECTEDVALUE ( 'Table'[Project] )
)

CD changes =
IF (
    CALCULATE (
        MIN ( 'Table'[Commissioning Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Project] = SELECTEDVALUE ( 'Table'[Project] )
        )
    )
        = SELECTEDVALUE ( 'Table'[Commissioning Date] ),
    BLANK (),
    SELECTEDVALUE ( 'Table'[Project] )
)

2.The output results of the two measures are shown in the figure:

Best Regards,

Charlotte Zhang

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

[MFelix · Accepted solution]

Hi @GJUDGE ,

Depending on how you are making the filter of the month but in this case I have added a disconnected table for the filtering of the month of reporting and did the following measure:

Has Changes = 
IF (
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                'Table',
                'Table'[Reporting Period] <= MAX ( 'Calendar'[Date] )
                    && 'Table'[Reporting Period]
                        >= DATE ( YEAR ( MIN ( 'Calendar'[Date] ) - 1 ), MONTH ( MIN ( 'Calendar'[Date] ) - 1 ), 1 )
            ),
            'Table'[Project],
            'Table'[Commissioning Date],
            'Table'[Construction Start Date]
        )
    ) > 1,
    "Project has changes"
)

Final result below

Check PBIX file attach.