Forum Discussion
SQL function like behavior using DAX
Hi Anonymous
According to the rule, it seems the line should be "current" instead of "future", 2018/10/21 is the Sunday of this week
ID Ship Date Quote Date Future 20021373QT 10/21/2018 10/19/2018 1 when you select a date range (date column from a disconnected table)
Create measures in the table
min = MIN(calendar[date])
weeknum-Ship Date = WEEKNUM(MAX([Ship Date]),2) weeknum-Quote Date = WEEKNUM(MAX([Quote Date]),2) currentweek = WEEKNUM([min]) state = SWITCH ( TRUE (), [weeknum-Ship Date] = [currentweek] && [weeknum-Quote Date] = [currentweek], "current", [weeknum-Ship Date] = [currentweek] && [weeknum-Quote Date] < [currentweek], "prior", [weeknum-Ship Date] > [currentweek] && [weeknum-Quote Date] = [currentweek], "future" )Best Regards
Maggie
I completely forgot to include some data and I apologize for that. However, I should probably also clarify that while the explanation states "this week", the date range may be any date range. The sample data below is from a date range of 10/15/18 - 10/21/18.
| Current | |||
| ID | Ship Date | Quote Date | Current |
| 20021334QT | 10/16/2018 | 10/15/2018 | 1 |
| 20021338QT | 10/16/2018 | 10/15/2018 | 1 |
| 20021340QT | 10/16/2018 | 10/16/2018 | 1 |
| 20021341QT | 10/16/2018 | 10/16/2018 | 1 |
| 20021342QT | 10/16/2018 | 10/16/2018 | 1 |
| 20021346QT | 10/17/2018 | 10/16/2018 | 1 |
| 20021347QT | 10/16/2018 | 10/16/2018 | 1 |
| 20021348QT | 10/17/2018 | 10/17/2018 | 1 |
| 20021349QT | 10/15/2018 | 10/17/2018 | 1 |
| 20021350QT | 10/17/2018 | 10/17/2018 | 1 |
| Prior | |||
| ID | Ship Date | Quote Date | Prior |
| 20021319QT | 10/18/2018 | 10/11/2018 | 1 |
| 20021326QT | 10/15/2018 | 10/12/2018 | 1 |
| 20021327QT | 10/15/2018 | 10/12/2018 | 1 |
| 20020937QT | 10/18/2018 | 8/30/2018 | 1 |
| 20021296QT | 10/16/2018 | 10/10/2018 | 1 |
| 20020376QT | 10/19/2018 | 7/17/2018 | 1 |
| 20021320QT | 10/19/2018 | 10/12/2018 | 1 |
| Future | |||
| ID | Ship Date | Quote Date | Future |
| 20021373QT | 10/21/2018 | 10/19/2018 | 1 |
| 20021375QT | 11/5/2018 | 10/19/2018 | 1 |
Hi Anonymous
According to the rule, it seems the line should be "current" instead of "future", 2018/10/21 is the Sunday of this week
| ID | Ship Date | Quote Date | Future |
| 20021373QT | 10/21/2018 | 10/19/2018 | 1 |
when you select a date range (date column from a disconnected table)
Create measures in the table
min = MIN(calendar[date])
weeknum-Ship Date = WEEKNUM(MAX([Ship Date]),2) weeknum-Quote Date = WEEKNUM(MAX([Quote Date]),2) currentweek = WEEKNUM([min]) state = SWITCH ( TRUE (), [weeknum-Ship Date] = [currentweek] && [weeknum-Quote Date] = [currentweek], "current", [weeknum-Ship Date] = [currentweek] && [weeknum-Quote Date] < [currentweek], "prior", [weeknum-Ship Date] > [currentweek] && [weeknum-Quote Date] = [currentweek], "future" )
Best Regards
Maggie