Forum Discussion

GJUDGE's avatar
GJUDGE
Icon for Helper II rankHelper II
4 years ago
Solved

Changes in Date MoM

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!

 

ProjectOutputConstruction Start DateCommissioning DateReporting Period
A5001/01/202301/06/2023Aug-21
B2501/04/202307/04/2023Aug-21
C5001/05/202301/07/2023Aug-21
A5001/02/202301/06/2023Sep-21
B2501/04/202307/04/2023Sep-21
C5001/05/202301/06/2023Sep-21
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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.

     

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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.

     

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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