Forum Discussion
Total YTD with USERELATIONSHIP
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 :
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)?No need for the inactive relationship. Add a filter to your calculation
Revenue YTD = CALCULATE(TOTALYTD(SUM(Revenue),Dim_Date[Date]),Client[Termination Date] in VALUES(Dim_Date[Date]))
2 Replies
- lbendlin
Super User
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)?No need for the inactive relationship. Add a filter to your calculation
Revenue YTD = CALCULATE(TOTALYTD(SUM(Revenue),Dim_Date[Date]),Client[Termination Date] in VALUES(Dim_Date[Date]))
- Claudine_MaFrequent Visitor
Thanks so much. This works ! 😊