DAX If Vlookup Statements

[HB2104]

Hello -

I am trying to create a DAX that is a multiple if statement vlook up. The statement is if CoCd column is KHLT & USD than lookup in Bank info column for correct bank info (in that statement, answer is 54764. Any idea is Power Bi can do that in DAX? I am newer using it. Thanks! 

CoCd	Currency	G/L	New Column			Company	Currency	Bank Info
KHLT	USD	200000	54764			LOMB	GBP	56765
KHLT	USD	200000	54764			LOMB	USD	34356354
KHLT	USD	200000	54764			LOMB	EUR & ALL OTHER	46354636
KHLT	GBP	200000	1241			KHLT	USD	54764
KHLT	GBP	200000	1241			KHLT	EUR	486785
LOMB	EUR	200000	46354636			KHLT	ALL OTHER	1241
LOMB	EUR	200000	46354636			JD	USD	3434534
LOMB	EUR	200000	46354636			JD	GBP	45634
LOMB	EUR	200000	46354636			JD	CNH	76572
LOMB	USD	200000	34356354			JD	MAD	23677
LOMB	GBP	200000	56765			JD	EUR & ALL OTHER	3532
LOMB	EUR	200000	46354636

[barritown · Accepted solution]

Hi @HB2104,

Try a calculated column like this:

And in plain text for convenience:

New Calculated Column = 
VAR _CurrentCoCd = [CoCd]
VAR _CurrentCurrency = [Currency]
RETURN COALESCE ( 
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && [Currency] = _CurrentCurrency ), [Bank Info] ),
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && CONTAINSSTRING ( [Currency], "ALL OTHER" ) ), [Bank Info] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[HB2104 · Accepted solution]

Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?

[barritown · Accepted solution]

Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂

[HB2104 · Accepted solution]

Thank you so much! Does it matter about the columns in these being "text" vs "number"? Like lets say I had the bank info and it wasn't just numbers it also had letters in it as well. 

[barritown · Accepted solution]

There shouldn't be any problems with text.

[barritown]

Yup, more examples would make the task more clear I guess.

Am I correct that in fact you extract 8 days from the due date, not 7?

9/19/23 - 8 days = 9/11/23 => ok, Monday, let's process
9/15/23 - 8 days = 9/7/23 => not ok, Thursday => process on next available payday, which is 9/8/23

Should [SAP Pulls # of Business Days], [Bank Clears # of Business Days] be used in calculations?

[HB2104]

Sure! Yes it's payment run day + 7 business working days that is correct.

SAP automated payment run days	Monday	Wednesday	Friday
SAP picks up documents due up to and including	Monday + 7 working days	Wednesday + 7 working days	Friday + 7 working days
Bank clearing day	Monday + 2 working days	Wednesday + 2 working days	Friday + 2 working days

Another example:

CoCd

Currency

Net Due Date

Day of Week

Calc Date

JDSP     GPB             9/28/23            Thursday

Calendar Rules 

JDSP
SAP automated payment run days	Tuesday	Wednesday
SAP picks up documents due up to and including	Tuesday + 7 working days	Wednesday+ 7 working days
Bank clearing day	Tuesday + 2 working days	Wednesday+ 2 working days

In that example, the invoice would be picked up on the date Wednesday 9/20. 

I am not worried about the clearing aspect as I think I will be able to work that out if a calculation for the working days is possible. Thank you!! 

[barritown]

It's possible. For example, please check the file available here - 

https://www.dropbox.com/scl/fi/n4rgr2xtqb7t61dm1g58n/community-pdate.pbix?rlkey=ct90a1xacdlrpiy9cr2t...

This solution requires more testing but seems to work:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[HB2104 · Accepted solution]

Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?

[barritown · Accepted solution]

Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂

[HB2104 · Accepted solution]

Thank you so much! Does it matter about the columns in these being "text" vs "number"? Like lets say I had the bank info and it wasn't just numbers it also had letters in it as well. 

[barritown · Accepted solution]

There shouldn't be any problems with text.

[HB2104 · Accepted solution]

Works great, thank you!

[barritown · Accepted solution]

Hi @HB2104,

Try a calculated column like this:

And in plain text for convenience:

New Calculated Column = 
VAR _CurrentCoCd = [CoCd]
VAR _CurrentCurrency = [Currency]
RETURN COALESCE ( 
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && [Currency] = _CurrentCurrency ), [Bank Info] ),
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && CONTAINSSTRING ( [Currency], "ALL OTHER" ) ), [Bank Info] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[HB2104]

I have a similar formula needed but this time I need to use date parameters and not sure if DAX can do that. Sample data:

CoCd	Currency	Net Due Date	Day of Week	Calc Date
KHLT	USD	9/19/23	Tuesday	?

LOMB

EUR

9/15/23

Friday

?

Calendar Rules

Company Code	SAP Payment Run Day	SAP Pulls # of Business Days	Bank Clears # of Business Days
KHLT	Monday	7	2
KHLT	Wednesday	7	2
KHLT	Friday	7	2

So for the first example, on 9/19/23 due date will be pulled on Monday 9/11/23 (prior 7 business days) there is a payment run and that invoice is pulled so the column calc date would be 9/11/12. The second example would be date 9/8/23. No idea is DAX can take into account calculating dates based on those parameters. 

[barritown]

@HB2104,

Sorry, but I don't understand the logic here and the role of Calendar Rules in your calculations.

Why is there no Tuesday and LOMB in the second table? 

Could you please re-explain in more details how it should work?

[HB2104]

The calendar rules are the work days that they do Accounts Payable payment runs, in this case they only run payments on Monday, Wednesday and Fridays. So if the due date is on 9/19/23, SAP will pick this invoice up Monday the 11th.

In that second example, the due date is 9/15/23 and 7 days prior to that would be 9/7/23 but that falls on a Thursday and SAP won't pick it up since it's not a payment run day. So the answer would be the following day, based on the calendar rules Friday the 8th it would be picked up for payment. Super complex and unsure a formula can be created for that logic. 😅

[barritown]

Are these Mon-Wed-Fri paydays applicable for all the companies? Otherwise, why are there no lines with "LOMB" in the second table?

[HB2104]

LOMB has a payment run every day of the work week, I should have put that in there. There are a few others outside the sample I provided that are different such as just a payment run on Tuesdays.