cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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:

 

Picture1.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Calculated column returning earlier datestamp of another column and a filter

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

View solution in original post

4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Calculated column returning earlier datestamp of another column and a filter

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

Re: Calculated column returning earlier datestamp of another column and a filter

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,

 

 

Capture.PNG

 

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 Super Contributor
Super Contributor

Re: Calculated column returning earlier datestamp of another column and a filter

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

View solution in original post

v-huizhn-msft Super Contributor
Super Contributor

Re: Calculated column returning earlier datestamp of another column and a filter

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 441 members 4,375 guests
Please welcome our newest community members: