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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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