cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Calculate weekday based on due date

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,

1 ACCEPTED SOLUTION
Super User

Best Regards,

6 REPLIES 6
Super User

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,

Helper I

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?

Super User

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,

Helper I

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

Super User

Best Regards,

Helper I

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors