topic Re: DAX formula for staff member associated with most recent visit date in DAX Commands and Tips

DAX formula for staff member associated with most recent visit date

PBInewbie17 — Wed, 03 Aug 2022 20:26:23 GMT

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.

Re: DAX formula for staff member associated with most recent visit date

littlemojopuppy — Thu, 04 Aug 2022 00:35:45 GMT

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.

Re: DAX formula for staff member associated with most recent visit date

Bubble4502 — Tue, 09 Aug 2022 08:17:29 GMT

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:

Kind Regards,

Bubble

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

Re: DAX formula for staff member associated with most recent visit date

PBInewbie17 — Tue, 09 Aug 2022 16:44:28 GMT

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

Re: DAX formula for staff member associated with most recent visit date

Bubble4502 — Wed, 10 Aug 2022 06:36:42 GMT

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:

Kind Regards,

Bubble

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

Re: DAX formula for staff member associated with most recent visit date

PBInewbie17 — Wed, 10 Aug 2022 18:51:43 GMT

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.

Re: DAX formula for staff member associated with most recent visit date

Bubble4502 — Thu, 11 Aug 2022 02:13:54 GMT

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:

Kind Regards,

Bubble

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

Re: DAX formula for staff member associated with most recent visit date

PBInewbie17 — Thu, 11 Aug 2022 14:47:31 GMT

Thank you!! That worked.