Calculated column returning earlier datestamp of another column and a filter

[wes-shen-poal]

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

[v-huizhn-msft]

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]

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 · Accepted solution]

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

[v-huizhn-msft]

Hi @wes-shen-poal,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Thanks,
Angelia