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 th...
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.

     

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.