View Full Version : Cumulative Total Based on Inactive Relationship Dates using USERELATIONSHIP

2019-10-21, 04:22 PM

How can I wrap around the USERELATIONSHIP to get the cumulative total inactive relationship. I have four dates in my fact table

Planned, Forecast, Actual Submission, Actual Responded

Below are the Measures I created.

1. Total = COUNTROW([Table]
2. Planned =CALCULATE[Table], USERELATIONSHIP(Calendar[Date],Table[Planned]))
3. Planner Cum =IF(ISBLANK([Planned]), BLANK(), CALCULATE([Planned]),USERELATIONSHIP(Table[Planned], Calendar[Date]), FILTER(ALLSELECTED(Calendar[Date], Calendar[Date], Calendar[Date] <= MAX (Calendar[Date])))

For some reason the 3rd measure is proving a different cumulative value.

My 2nd measure provides the exact value. i.e

16-Dec - 2 count
17-Jan - 2 count

The 3rd measure should provide 2 total unfortunately it gives me 605 starting value theb 607 which correct incremental of 2 nos.

Any input is well appreciated. I cannot attach the file as it's exceed the allowed file size for upload. But please download from the link below.


Sent from my LYA-L29 using Tapatalk