topic Virtual Table get values where Date is less than X in DAX Commands and Tips

Virtual Table get values where Date is less than X

ruesaint_denis — Sat, 04 Jun 2022 20:55:02 GMT

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?

Re: Virtual Table get values where Date is less than X

ruesaint_denis — Sat, 04 Jun 2022 22:13:21 GMT

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?

Re: Virtual Table get values where Date is less than X

ruesaint_denis — Sun, 05 Jun 2022 04:54:47 GMT

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!