SQL function like behavior using DAX

[Anonymous]

This may have already been addressed, however, I am still working on my DAX education so I’m sure I just don’t understand enough to realize I passed over the information. Thanks in advance for any help possible.

I have a MS SQL query that utilizes a function that requires start date and end date parameters. The way the function works is essentially when specific dates run within the date range the data will be flagged as either current, prior or future. Below are the criteria for each flag:

• Current – If the ShipDate and QuoteDate is between Monday (first day of our week) of this week and Sunday (last day of our week).
• Prior – If the ShipDate is between Monday and Sunday of this week and the QuoteDate is prior to the Monday of this week.
• Future – If the ShipDate is after the Sunday of this week and the QuoteDate is between Monday and Sunday of this week.

My report is utilizing the Date Slicer to create the Date Range for several other pages, however, I cannot seem to figure out how to duplicate the behavior of the function in Power BI and utilize the Date Slicer. Is this possible in Power BI or am I reaching at this point?

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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

[Greg_Deckler]

Can you provide sample data? Do you have a Calendar table? You can use WEEKDAY and WEEKNUM and TODAY to figure out most of the base data that you will need to recreate this in DAX but hard to provide specific instructions without knowing your data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490