How to fix Cumulative Measures not working as expected

Question

I'm attempting to show two cumulative measures on a graph, however only one is showing correctly. The other is not counting up it's values correctly.

One measure is calculating the cumulative expected submissions.

The other is for the total actual submissions.

Within both of the cumulative measures I have played it safe and used the 'USERELATIONSHIP' function.

I have created an independent date table that covers the necessary date range. I have created two relationships between the date table and the table containing the expected & actual submissions. The main relationship is between the date table and the expected submissions. The second relationship is between the date and the actual submissions.

Data:

Expected Submission End of Week (Site) Actual Submission End of Week

24/05/2019 24/05/2019

07/06/2019
15/03/2019 15/03/2019

07/06/2019
21/06/2019
14/06/2019
19/04/2019 19/04/2019

07/06/2019
14/06/2019
14/06/2019
07/06/2019
28/06/2019
07/06/2019
24/05/2019 24/05/2019

21/06/2019
14/06/2019
07/06/2019
28/06/2019
28/06/2019
21/06/2019
24/05/2019 24/05/2019

21/06/2019
07/06/2019
19/04/2019 07/06/2019

17/05/2019 17/05/2019

15/03/2019 15/03/2019

Cumulative Actual O&M Submission = 
CALCULATE (
        COUNTA(  'O&M - Tracker'[Actual Submission End of Week]),

        FILTER (
            ALL('O&M - Tracker'),
            'O&M - Tracker'[Actual Submission End of Week] <= MAX(  'O&M - Tracker'[Actual Submission End of Week])
        ),
        USERELATIONSHIP(OMDATES2[Date],'O&M - Tracker'[Actual Submission End of Week])

)


Cumulative Expected O&M Submission =
CALCULATE (
        COUNTA(  'O&M - Tracker'[Expected Submission (Site)]),

        FILTER (
            ALL('O&M - Tracker'),
            'O&M - Tracker'[Expected Submission End of Week (Site)] <= MAX ( 'O&M - Tracker'[Expected Submission End of Week (Site)])
        ),
        USERELATIONSHIP(OMDATES2[Date],'O&M - Tracker'[Expected Submission End of Week (Site)])
    )

The issue I am having is that the cumulative measure of actual submissions doesn't plot correctly and it appears to be due to the relationship of date to actual submission not being the main relationship. (when I switch the relationship hierarchy, the other cumulative measure goes wrong).

Current results:

Cumulative Actual (incorrect) = 2 (15/03/2019), 7 (19/04/2019), 4 (17/05/2019), 7 (24/05/2019)

Cumulative Expected = 2, 4, 5, 8, 15, 19, 23, 26

Desired results:

Cumulative Actual = 2 (15/03/2019), 3 (19/04/2019), 4 (17/05/2019), 7 (24/05/2019), 8 (07/06/2019)

Cumulative Expected = 2, 4, 5, 8, 15, 19, 23, 26

If anyone can help explain where I am going wrong, it would greatly be appreciated.

Paul

@RADO The Chart axis contains the Date field from my dates table OMDATES2. This table is a single column of dates derived from the minimum date of either the min expected submission date, or min actual submission date. To the maximum date of either the max expected submission date, or max actual submission date. — paulaw
Ok, got it. I understand what the problem is and will write you an answer in a few minutes. — RADO
@RADO Brilliant, thanks so much for responding so quickly and explaining. By having my previous date table generated by reference to the dates within the O&M table, are you saying that this still forms a relationship although the resulting date table appears independent? Is there any way to dynamically set the range of my new date table so that it's min and max do not go beyond the min/max of the O&M table, similar to what I previously had? — paulaw
Relationships between your tables are set by you; it does not matter how the tables are generated. Regarding min/max - I would recommend you to use Power Query for that. For example: medium.com/@Konstantinos_Ioannou/… — RADO

RADO RADO · Accepted Answer · 2019-06-04T14:00:23

The key problem here is that you are using the wrong dates - they need to be from the Date table, not "O&M - Tracker" table. Otherwise, you can't use "USERELATIONSHIP" - because you are not using "Date" table at all.

I would solve the problem as follows (I'll use simplified names here, change them as needed):

Cumulative Expected Submissions =
VAR 
   Current_Date = MAX ( 'Date'[Date] )
RETURN
   CALCULATE ( COUNTA ( Submissions[Expected] ), 'Date'[Date] <= Current_Date )

where "Date" is the name of your date table. No need for "USERELATIONSHIP" here.

Similarly:

Cumulative Actual Submissions =
VAR 
   Current_Date = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Submissions[Actual] ),
        'Date'[Date] <= Current_Date,
        USERELATIONSHIP ( 'Date'[Date], Submissions[Actual] )
    )

How it works: The same way as your formulas, but instead of accumulating over "O&M - Tracker" table, we accumulate over your date table. That allows us to use different relations.

How to fix Cumulative Measures not working as expected

1 Answers