Stop cumulative line when there is no value

[Anonymous]

Hi there, my cumulative sum is extending to further dates where there are blank values. I need it to stop when there is no more data. 

I attached my example PBI file here : PBI share 

it's a cumulative line graph shoing forecast, actual and plan by week.

hope this is clear question for you. 

any ideas would be much appreciated.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jdbuchanan71]

@Anonymous 

You can add a check to each measure for the MAX date, something like so:

CSU SP_ROS = 
VAR _End = CALCULATE ( MAX ( Sheet2[ROS] ), ALL ( 'Date' ) )
RETURN
    IF (
        MAX ( 'Date'[Date] ) > _End,
        BLANK (),
        CALCULATE (
            SUM ( 'Sheet2'[ROS ITEM] ),
            USERELATIONSHIP ( Sheet2[ROS], 'Date'[Date] ),
            FILTER (
                ALLSELECTED ( 'Date'[Date] ),
                ( 'Date'[Date] <= MAX ( 'Date'[Date] ) )
            )
        )
    )

[Anonymous]

@jdbuchanan71  Thanks for your reply.

This code works what i want to show but there is 1 out of 4 lines in the graph stops somehow 1 week early.

the IRN is supposed to stop W34 with value 202 but it stops W33. do you know why this code didn't count W34 for IRN?

[jdbuchanan71 · Accepted solution]

@Anonymous 

That is because the MAX Sheet2[IRN] is August 21 but the MAX of week 2020-34 is August 22.  If we adjust it like so it will show up.

CSU SP_IRN = 
VAR _End = CALCULATE ( MAX ( Sheet2[IRN] ), ALL ( 'Date' ) )
RETURN
    IF (
        _End < MIN ( 'Date'[Date] ),
        BLANK (),
CALCULATE(
	COUNTA('Sheet2'[IRN]), USERELATIONSHIP(Sheet2[IRN],'Date'[Date]),
	FILTER(
		ALLSELECTED('Date'[Date]),
		('Date'[Date] <= MAX('Date'[Date])))))

Basically we are looking if the _End is before the start of the week rather than the end of the week.

[Greg_Deckler]

@Anonymous - Didn't my code work for this? (attached)

[Anonymous]

HI @Greg_Deckler  

I have 4 lines in the graph. 1 line value doesn't appear properly.

The orange line is supposed to stop in week 34 with cumul vaule 202 but when i used your code it stops week 33 and the final vaule is 62, somehow the code didn't count w34 values and stop w33

Do you know why?

See the attached file -> PBI LINK 

[Greg_Deckler]

@Anonymous - It does not stop, it just happens to be very close to another color's value for most of the graph. See attached PBIX file updated where I move that measure to a column so that you can see it continues. I don't see the code that stops it that I wrote in the PBIX file.

[Anonymous]

@Greg_Deckler 

I checked as well after removing other lines. It stops W33 even though it has value till W34.

Can you please link the file again for me? i got error message and can't open it in my desktop.