Calculate weekday based on due date

[KnowNow]

I need to create a dax measure that shows the closest Tuesday and Thursday of every week from an invoice due date. For example, if the invoice due date is Saturday, 09/30/2023, then the closet Tuesday is 10/3/2023. If the invoice due date is Wednesday, 10/4/2023, then the closest Thursday is 10/5/2023. I only need these dates to fall on Tuesday or Thursday.

Thanks,

[rsbin]

@KnowNow ,

The simplest way I would do this is to use the SWITCH function:

ClosestTueorThurs = SWITCH(
                        TRUE(),
                        WEEKDAY( [InvoiceDueDate] ) = 1, [InvoiceDueDate] + 2,
                        WEEKDAY( [InvoiceDueDate] ) = 2, [InvoiceDueDate] + 1,
                        WEEKDAY( [InvoiceDueDate] ) = 3, [InvoiceDueDate] + 0,
                        WEEKDAY( [InvoiceDueDate] ) = 4, [InvoiceDueDate] + 1,
                        WEEKDAY( [InvoiceDueDate] ) = 5, [InvoiceDueDate] + 0,
                        WEEKDAY( [InvoiceDueDate] ) = 6, [InvoiceDueDate] + 4,
                        WEEKDAY( [InvoiceDueDate] ) = 7, [InvoiceDueDate] + 3 )

I have made the assumption that if your InvoiceDueDate falls on a Tuesday or Thursday, the closest day is that date.  You can alter the Switch Statement accordingly if that assumption is incorrect.
I have used the default type to indicate Sunday = 1.

Hope this helps.

Regards,

[KnowNow]

I am new to dax. I need to select the due date from the invoice table but it is not allowing me. Is there another keword that I need for the measure to allow me to select the due date from the invoice table?

[rsbin]

@KnowNow ,

Apologies, I should have clarified that this is a Calculated Column added to your Invoice Table - not a Measure.

Have attached a simple pbix file so you can see more clearly.

Regards,

[KnowNow]

I'm sorry. I did not type the formula correctly. After correcting the formula, it works, now. Thank you so much.

[rsbin · Accepted solution]

@KnowNow ,

Please Accept as Solution to close out the thread.

Best Regards,

[KnowNow]

Thank you but now I am getting the error "switch does not support comparing values of type true/false with values of type integer. Consider using the VALUE or FORMAT function to convert one of the values