Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |