Forum Discussion
Helper ICalculate 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,
6 Replies
- rsbin
Community Champion
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,
KnowNowI 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?