Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Icon for Helper III rankHelper III
8 years ago
Solved

Calculated column returning earlier datestamp of another column and a filter

Hi there,

 

I would like to create a calculated column named DwellTimeOutAdjusted in the 'container_dwell' data table

 

what I want the column to do for each 'N4 ActiveUnits'[UnitID] is to take the earlier datestamp of 'container_dwell'[DwellTimeOut] column and the  'N4 ActiveUnits'[SnapshotDate] filter

 

BUT

 

if the 'N4 ActiveUnits'[SnapshotDate] filter has a selected date that is earlier than the UnitID's 'container_dwell'[DwellTimeIn]

datestamp than the new column should take the 'container_dwell'[DwellTimeIn] datestamp instead

 

Below are the scenarios:

 

 

Would someone be able to help me with the calculated column formula?

 

Thanks

Wes

 

ps. the 'container_dwell' data table and the 'N4 ActiveUnits' table are bidirectionally related on UnitID as primary key

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi wes-shen-poal,

    You SnapshotDate column as a page filter, it will affect all report in the page. And you want to create a dynamic column based on the filter, we can't do this, please see the reason in this knowledage base. I have been considering use a measure, it there is one row after you select one date in filter, please try the following formula, and check if it works fine.

    DwellTimeOut =
IF (
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    )
        <= CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate ]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    ),
    IF (
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        )
            >= CALCULATE (
                FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
                ALLSELECTED ( 'container_dwell' )
            ),
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
            ALLSELECTED ( 'container_dwell' )
        )
    )
)


    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi wes-shen-poal,

    The relationship between 'container_dwell' data table and the 'N4 ActiveUnits' is one-to-one relationship? If it is, you can create a calculated column to get the 'N4 ActiveUnits'[SnapshotDate] in 'container_dwell' data table using related function. 

    SnapshotDate filter=RELATED('N4 ActiveUnits'[SnapshotDate])


    Then create another calculated column using the formula below to get what you desired.

    DwellTimeOut =
IF (
    'container_dwell'[DwellTimeOut] <= 'container_dwell'[SnapshotDate filter],
    'container_dwell'[DwellTimeOut],
    IF (
        'container_dwell'[SnapshotDate filter] >= 'container_dwell'[DwellTimeIn],
        'container_dwell'[SnapshotDate filter],
        'container_dwell'[DwellTimeIn]
    )
)


    If this desn't resolve your issue, please share more details for further analysis.

    Best Regards,
    Angelia

    • wes-shen-poal's avatar
      wes-shen-poal
      Icon for Helper III rankHelper III
      8 years ago

      Hi v-huizhn-msft

       

      Thanks for the message. I don't think the calculated columns will work?

       

      Sorry to confuse you.

       

      I am using SnapshotDate column as a page filter.

       

      So if we refer to screenshot below as an example:

       

      For UnitID 2719892,

       

       

       

      Because I've selected "31/10/2017 6:10:01PM" in the SnapshotDate filter, it would also be "31/10/2017 6:10:01PM" in new DwellTimeOutAdjusted column because my SnapshotDate filter is

      (1) earlier than DwellTimeOut (="2/11/2017 2:38:56 PM") , AND,

      (2) later than my DwellTimeIn (="28/10/2017 2:17:59 AM")

       

      If I selected, e.g. "25/10/2017 4:50:01 AM" in my SnapshotDate filter then it would be "28/10/2017 2:17:59 AM" in my new DwellTimeOutAdjusted column because my SnapshotDate filter is

      (1) earlier than my DwellTimeIn (="28/10/2017 2:17:59 AM")

       

      If I selected, e.g. "3/11/2017 5:10:01 PM" in my SnapshotDate filter then it would be "2/11/2017 2:38:56 PM" in my new DwellTimeOutAdjusted column because my SnapshotDate filter is

      (1) later than DwellTimeOut (="2/11/2017 2:38:56 PM")

       

      So this new DwellTimeOutAdjusted column is dynamic, and significantly dependent on what is selected in the SnapshotDate filter to then decide which of three date/timestamps to take (DwellTimeIn, DwellTimeOut, SnapshotDate) as the DwellTimeOutAdjusted for each UnitID

       

      Hope that makes more sense.

       

      Regarding the table relationship:

      I have a container_dwell 1-to-many N4 ActiveUnits relationship.

       

      Thanks

      Wes

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi wes-shen-poal,

        You SnapshotDate column as a page filter, it will affect all report in the page. And you want to create a dynamic column based on the filter, we can't do this, please see the reason in this knowledage base. I have been considering use a measure, it there is one row after you select one date in filter, please try the following formula, and check if it works fine.

        DwellTimeOut =
IF (
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    )
        <= CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate ]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    ),
    IF (
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        )
            >= CALCULATE (
                FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
                ALLSELECTED ( 'container_dwell' )
            ),
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
            ALLSELECTED ( 'container_dwell' )
        )
    )
)


        Best Regards,
        Angelia