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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
IrishChristof
Frequent Visitor

DAX Values are before one date column, after another date column

Hi, I am trying to put together a report that looks at payments relating to customers who havent been on or returned from a trip.

I have a date table (DatesTable[Date]) linked to Payment Date (Pay[PayDate]), is it possible to also filter the returned date column (Pay[ReturnedDate])... I am using PowerPivot.

 

This is what I've tried...

<code>

VAR SelectedDate = MAX(DatesTable[Date])
VAR CumultiveReceipts =
FILTER (
'Pay',
'Pay'[PayDate]
<= SelectedDate
&& 'Pay'[PayDate] <= SelectedDate
&& 'Pay'[ReturnedDate] >= SelectedDate
)
RETURN
SUMX ( CumultiveReceipts, [YTDPay])

</code>

 

YTDPay is taking the total cumulative receipts based on my selected date..

<code>

YTDPay:=CALCULATE([NetReceiptsPay],

FILTER(ALL(DatesTable)

,DatesTable[Date] <= MAX(DatesTable[Date]) && DatesTable[Date] > MAX(DatesTable[Date])-7000

)

)

</code>

 

Anyone able to help?

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

hi @IrishChristof 

 

it is possible to have more than 1 relationship between to tables. E.g. you can create a relationship between DatesTable[Date] and Pay[ReturnedDate], in the model view you will see that the line representing this relationship is dashed. This means that the relationship is inactive. In a measure you can activate it by using the USERELATIONSHIP-function

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Thanks for the guidance.. I have created the relationship in the model (one active, and one inactive)..

 

How would I go about using the USERELATIONSHIP function to show sum of revenue ( Pay[RevenueGBP]) if Pay[PaymentDate] is before 13th February and Pay[ReturnDate] is after 13th February if I selected 13th February in DatesTable[Date]

 

ie: a list of payments received for customers who havent returned 

?

Thanks

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.