Showing results for 
Search instead for 
Did you mean: 

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

Helper I
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.




@KnowNow ,

Please Accept as Solution to close out the thread.

Best Regards,

View solution in original post

Super User
Super User

@KnowNow ,

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

ClosestTueorThurs = SWITCH(
                        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.


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?

@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.


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

@KnowNow ,

Please Accept as Solution to close out the thread.

Best Regards,

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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