Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply

Virtual Table get values where Date is less than X

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? 

1 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!

View solution in original post

2 REPLIES 2

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: 

 

ruesaint_denis_0-1654380792737.png

 

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

 

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors