Virtual Table get values where Date is less than X

[ruesaint_denis]

Hi everyone, 

In part of a larger DAX I'm writing, I'm trying to isolate the VALUES(), or a virtual table with one column that has the dates prior to X. 

For example, instead of this, which only isolates the previous month:

VAR previousMonthDates = PREVIOUSMONTH('Sales table'[date])

I am trying to have something like this, which isolates all dates prior to a given date: 

CALCULATE(VALUES('Date Table'[Date]), Filter('Date Table','Date Table'[Date]<DATE(2020,05,01))

I am asking to receive the VALUES() of the column Date Table'[Date] if they occur prior to 5/1/2020.. but I get this error: 

The expression specified in the query is not a valid table expression.

My larger scale goal is to get the USER IDs of ppl that completed orders prior to a given date: 

VAR previousMonthsDates = CALCULATE(VALUES('Date Table'[Date]), Filter('Date Table','Date Table'[Date]<DATE(2020,05,01))

VAR previousMonthCustomers = CALCULATETABLE(VALUES(Orders[User Id]), 
    ALL('Date Table'[Date])
    , previousMonthsDates, USERELATIONSHIP(Orders[Completed At],'Date Table'[Date])
)​

The intention of the above DAX is to get the VALUES() of the USER IDs for those that completed orders within the previousMonthsDates

Any ideas of how I can get arond this? 

[ruesaint_denis]

BTW this worked when creating a calculated table, but when I try to create measure it gives me an error

Calcuated Table that works to isolate the USER Ids that : 

VAR currentCustomers = CALCULATETABLE(VALUES(Orders[User Id]), filter(Orders, Orders[Completed At]>=DATE(2020,04,01)&&Orders[Completed At]<DATE(2020,05,01)))
VAR previousMonthDates = SUMMARIZECOLUMNS ( 
    'Date Table'[Date], 
    FILTER('Date Table', 'Date Table'[Date]<DATE(2020,04,01))) 


VAR previousMonthCustomers = CALCULATETABLE(VALUES(Orders[User Id]), 
    ALL('Date Table'[Date])
    , previousMonthDates, USERELATIONSHIP(Orders[Completed At],'Date Table'[Date])
)

VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)

RETURN 
repeatCustomersPreviousMonth

RETURN 
repeatCustomersPreviousMonth

Measure throws error: SummarizeColumns() and AddMissingItems() may not be used in this context

VAR currentCustomers = VALUES('Orders'[User Id])
VAR previousMonthDates = SUMMARIZECOLUMNS ( 
    'Date Table'[Date], 
    FILTER('Date Table', 'Date Table'[Date]<DATE(2020,05,01))) 


VAR previousMonthCustomers = CALCULATETABLE(VALUES(Orders[User Id]), 
    ALL('Date Table'[Date])
    , previousMonthDates, USERELATIONSHIP(Orders[Completed At],'Date Table'[Date])
)

VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)

RETURN 
SUMX(repeatCustomersPreviousMonth, [Total Completed Sales]) 

the measure seems to work when I thorugh it in a card, but when I'm trying to get a table or a matrix it gives the error. This is my table: 

😭I'm so close yet so far away, how can i address this?

[ruesaint_denis · Accepted solution]

Hello, I solved it, I used DATESBETWEEN

VAR MinDate = MIN ( 'Date Table'[Date] )-1
VAR currentCustomers = CALCULATETABLE(VALUES('Orders'[User Id]), Orders[Status]="Completed", USERELATIONSHIP(Orders[Completed At],'Date Table'[Date]))
VAR previousMonthDates =  DATESBETWEEN('Date Table'[Date], BLANK(),  MinDate) 


VAR previousMonthCustomers = CALCULATETABLE(distinct(Orders[User Id]),ALL('Date Table'[Date]), previousMonthDates, Orders[Status]="Completed", USERELATIONSHIP(Orders[Completed At],'Date Table'[Date])
)


VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)

RETURN 
calculate(SUMX(repeatCustomersPreviousMonth, [Total Completed Sales]))

Cheers!