Cumulative Total Based on Inactive Relationship Dates using USERELATIONSHIP

mielkew

New member
Joined
Feb 18, 2018
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

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(

2. Planned =CALCULATE
, 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.
https://www.dropbox.com/s/w4kml89wzomowgi/Reporting_working.xlsx?dl=0

Thanks,
Edward
4e083033ca8588e8ee316fef4a3fec68.jpg


Sent from my LYA-L29 using Tapatalk
 
Top