Hey all,
I struggle with some DAX and would appreciate a little help as I can´t seem to figure this one out.
So I have a client table (Dimension Table) where some clients have terminated their contracts on a certain date :
| Client | Termination | Termination Date |
| Client A | Yes | 15.6.2023 |
| Client B | No | |
| Client C | Yes | 15.6.2024 |
This table has an inactive relationship to my Dim_Date Table
Then I have a fact table with revenues like this :
| Client | Booking Date | Revenue |
| Client B | 15.1.2024 | 100€ |
| Client C | 15.1.2024 | 1000€ |
| ..... | | |
This table has an active relationsjip to my Dim_Date Table (Booking Date > Date)
In my overall reporting page I have some Revenue YTD Measure working well : Revenue YTD = TOTALYTD(SUM(Revenue),Dim_Date[Date])
Now I have been asked to add a page about the revenue loss due to termination. Therefore I would like to calcultate the Revenue YTD only for terminated clients. At the top I have a date slicer (Between).
How can I write a DAX measure where the date slice affects both the revenue YTD (revenue only for the selected timeframe) and the termination date (only clients terminated in the selected time range)?
When doing the following measure all revenues are counted unrelated to the selected timeframe :
CALCULATE([Revenue YTD], dim_clients[Termination]="Yes", USERELATIONSHIP(dim_Date[Date], dim_clients[Terminate Date]))
I have tried a few other things but nothing seems to work so I will spare you those.
Thanks for you help
Claudine
