Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PBInewbie17
Helper I
Helper I

DAX formula for staff member associated with most recent visit date

Hello,

 

I’m trying to determine what DAX formula would be used to create a new column that calculates the staff member associated with the most recent visit date. There are 6 total columns: 3 for each position's visit date and 3 with the associated position's staff member.

 

Appraiser Visit Date

Appraiser Visit Staff

Construction Visit Date

Construction Visit Staff

Underwriter Visit Date

Underwriter Visit Staff

7/22/2022

Tom Jones

7/22/2022

Tom Jones

7/22/2022

Tom Jones

 

 

 

Peter Parker

 

 

 

 

 

 

12/3/2020

Jane Smith

 

Kyle Le

8/2/2017

Kyle Le

 

 

 

 

 

 

 

 

4/11/2021

Hugo Jones

4/11/2021

Hugo Jones

 

 

6/7/2018

Johnny Appleseed

 

 

10/9/2019

Parker Collins

 

Here’s where it gets tricky:

  • Some rows are completely blank. For those rows, I would like it to calculate a blank.
  • Several rows have the exact same visit dates and staff members listed.
  • There may be a staff member listed, but no associated date. For these items, I still want to pull the staff member’s name.

I appreciate any guidance.

2 ACCEPTED SOLUTIONS
Bubble4502
Resolver III
Resolver III

Hi @PBInewbie17 ,

 

Please try:

the most recent visit date =
VAR _a =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        DAY
    )
VAR _b =
    DATEDIFF (
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _c =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, [Appraiser Visit Date], [Underwriter Visit Date] ),
        IF ( _b <= 0, [Construction Visit Date], [Underwriter Visit Date] )
    )
RETURN
    IF ( _d = DATE ( 1899, 12, 31 ), BLANK (), _d )

Output:

Bubble4502_0-1660033032463.png

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Bubble4502
Resolver III
Resolver III

Hi @PBInewbie17 ,

 

Please try:

Most Recent Visit Date =
VAR _a1 =
    IF ( ISBLANK ( 'Table'[Date 1] ), DATE ( 1899, 12, 31 ), 'Table'[Date 1] )
VAR _a2 =
    IF ( ISBLANK ( 'Table'[Date 2] ), DATE ( 1899, 12, 31 ), 'Table'[Date 2] )
VAR _a3 =
    IF ( ISBLANK ( 'Table'[Date 3] ), DATE ( 1899, 12, 31 ), 'Table'[Date 3] )
VAR _a =
    DATEDIFF ( _a1, _a2, DAY )
VAR _b =
    DATEDIFF ( _a2, _a3, DAY )
VAR _c =
    DATEDIFF ( _a1, _a3, DAY )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, 'Table'[Date 1], 'Table'[Date 3] ),
        IF ( _b <= 0, 'Table'[Date 2], 'Table'[Date 3] )
    )
RETURN
    IF ( _d = DATE ( 1899, 12, 31 ), BLANK (), _d )


Associated Name = 
VAR _a1 =
    IF ( ISBLANK ( 'Table'[Date 1] ), DATE ( 1899, 12, 31 ), 'Table'[Date 1] )
VAR _a2 =
    IF ( ISBLANK ( 'Table'[Date 2] ), DATE ( 1899, 12, 31 ), 'Table'[Date 2] )
VAR _a3 =
    IF ( ISBLANK ( 'Table'[Date 3] ), DATE ( 1899, 12, 31 ), 'Table'[Date 3] )
VAR _a =
    DATEDIFF ( _a1, _a2, DAY )
VAR _b =
    DATEDIFF ( _a2, _a3, DAY )
VAR _c =
    DATEDIFF ( _a1, _a3, DAY )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, 'Table'[Name 1], 'Table'[Name 3] ),
        IF ( _b <= 0, 'Table'[Name 2], 'Table'[Name 3] )
    )
RETURN
    IF (
        _d = BLANK (),
        SWITCH (
            TRUE (),
            'Table'[Name 1] = BLANK ()
                && 'Table'[Name 3] = BLANK (), 'Table'[Name 2],
            'Table'[Name 1] = BLANK ()
                && 'Table'[Name 2] = BLANK (), 'Table'[Name 3],
            'Table'[Name 2] = BLANK ()
                && 'Table'[Name 3] = BLANK (), 'Table'[Name 1]
        ),
        _d
    )

Final output:

Bubble4502_0-1660184021930.png

 

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Bubble4502
Resolver III
Resolver III

Hi @PBInewbie17 ,

 

Please try:

Most Recent Visit Date =
VAR _a1 =
    IF ( ISBLANK ( 'Table'[Date 1] ), DATE ( 1899, 12, 31 ), 'Table'[Date 1] )
VAR _a2 =
    IF ( ISBLANK ( 'Table'[Date 2] ), DATE ( 1899, 12, 31 ), 'Table'[Date 2] )
VAR _a3 =
    IF ( ISBLANK ( 'Table'[Date 3] ), DATE ( 1899, 12, 31 ), 'Table'[Date 3] )
VAR _a =
    DATEDIFF ( _a1, _a2, DAY )
VAR _b =
    DATEDIFF ( _a2, _a3, DAY )
VAR _c =
    DATEDIFF ( _a1, _a3, DAY )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, 'Table'[Date 1], 'Table'[Date 3] ),
        IF ( _b <= 0, 'Table'[Date 2], 'Table'[Date 3] )
    )
RETURN
    IF ( _d = DATE ( 1899, 12, 31 ), BLANK (), _d )


Associated Name = 
VAR _a1 =
    IF ( ISBLANK ( 'Table'[Date 1] ), DATE ( 1899, 12, 31 ), 'Table'[Date 1] )
VAR _a2 =
    IF ( ISBLANK ( 'Table'[Date 2] ), DATE ( 1899, 12, 31 ), 'Table'[Date 2] )
VAR _a3 =
    IF ( ISBLANK ( 'Table'[Date 3] ), DATE ( 1899, 12, 31 ), 'Table'[Date 3] )
VAR _a =
    DATEDIFF ( _a1, _a2, DAY )
VAR _b =
    DATEDIFF ( _a2, _a3, DAY )
VAR _c =
    DATEDIFF ( _a1, _a3, DAY )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, 'Table'[Name 1], 'Table'[Name 3] ),
        IF ( _b <= 0, 'Table'[Name 2], 'Table'[Name 3] )
    )
RETURN
    IF (
        _d = BLANK (),
        SWITCH (
            TRUE (),
            'Table'[Name 1] = BLANK ()
                && 'Table'[Name 3] = BLANK (), 'Table'[Name 2],
            'Table'[Name 1] = BLANK ()
                && 'Table'[Name 2] = BLANK (), 'Table'[Name 3],
            'Table'[Name 2] = BLANK ()
                && 'Table'[Name 3] = BLANK (), 'Table'[Name 1]
        ),
        _d
    )

Final output:

Bubble4502_0-1660184021930.png

 

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you!! That worked.

Bubble4502
Resolver III
Resolver III

Hi @PBInewbie17 ,

 

Please try:

the most recent visit Staff =
VAR _a =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        DAY
    )
VAR _b =
    DATEDIFF (
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _c =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, [Appraiser Visit Staff], [Underwriter Visit Staff] ),
        IF ( _b <= 0, [Construction Visit Staff], [Underwriter Visit Staff] )
    )
RETURN
    IF (
        [Appraiser Visit Staff] = BLANK (),
        SWITCH (
            TRUE (),
            [Appraiser Visit Staff] = BLANK ()
                && [Underwriter Visit Staff] = BLANK (), [Construction Visit Staff],
            [Appraiser Visit Staff] = BLANK ()
                && [Underwriter Visit Staff] = BLANK (), [Construction Visit Staff],
            [Construction Visit Staff] = BLANK ()
                && [Underwriter Visit Staff] = BLANK (), [Appraiser Visit Staff]
        ),
        _d
    )

Output:

Bubble4502_0-1660113391582.png

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Bubble,

 

Thank you, but I tried your solutions and they do not account for additional variables in my data. I've created a new table that includes 3 dates with corresponding names. The goal is the same: calculate the most recent date and the associated name that goes with that date. I am not sure how to paste the DAX code in here, so I've provided screenshots of both the table and the formulas I used. Thank you.

PBInewbie17_0-1660157135613.png

PBInewbie17_1-1660157419495.pngPBInewbie17_2-1660157434557.png

 

 

Bubble4502
Resolver III
Resolver III

Hi @PBInewbie17 ,

 

Please try:

the most recent visit date =
VAR _a =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        DAY
    )
VAR _b =
    DATEDIFF (
        IF (
            ISBLANK ( [Construction Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Construction Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _c =
    DATEDIFF (
        IF (
            ISBLANK ( [Appraiser Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Appraiser Visit Date]
        ),
        IF (
            ISBLANK ( [Underwriter Visit Date] ),
            DATE ( 1899, 12, 31 ),
            [Underwriter Visit Date]
        ),
        DAY
    )
VAR _d =
    IF (
        _a <= 0,
        IF ( _c <= 0, [Appraiser Visit Date], [Underwriter Visit Date] ),
        IF ( _b <= 0, [Construction Visit Date], [Underwriter Visit Date] )
    )
RETURN
    IF ( _d = DATE ( 1899, 12, 31 ), BLANK (), _d )

Output:

Bubble4502_0-1660033032463.png

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you, this helps. Once I find the most recent visit date, how do I calculate the corresponding staff person with that visit date?

littlemojopuppy
Community Champion
Community Champion

Hi @PBInewbie17.  Your data model is wrong.  What you want to have is a big list of visit types, dates and who was associated with that particular visit.  If you correct your data model, this isn't very difficult.  But with how your data is laid out, it's a nightmare to solve.

 

Reformat your data model so that tables are long but narrow, not wide.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors