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.
Solved! Go to Solution.
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.
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?
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.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.