Stopping a Cumulative Earned Value Measure at a Certain Point

[Dylo_Bloomfield]

Hi,

I need some help stopping my Cumulative Earned Value measure to stop at a certain Week Ending. 

See below snippet.

I have drawn a red line on where I need the earned value line should stop. I need it to be stopped on the Week Ending 01/09/2023, have tried a couple of things but they all seem to just strertch the graph out and become a straight line. 

I also have the Data Table linked to the Calendar Milestone table. 

Below is the current formula being used. 

Cumulative EV Labour =

CALCULATE(

    [Earned Labour],

    FILTER(

        ALLSELECTED('Calendar (Milestone Source)'[Week Ending]),

        ISONORAFTER('Calendar (Milestone Source)'[Week Ending], MIN('Calendar (Milestone Source)'[Week Ending]), DESC)

    )

)

Thank you 🙂

[JoeBarry · Accepted solution]

In your Calendar table add a column called CurrentWE

CurrentWE = CALCULATE(MAX('Calendar'[Week Ending]), Filter('Calendar', 'Calendar'[Date] = TODAY()))

Then the new RunningTotal Measure

Cumulative EV Labor =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE([Earned Labor],
KEEPFILTERS( 'Calendar'[Date] <= MaxDate && MaxDate < = 'Calendar'[Current WE]  ),
ALL('Calendar'))

 Thanks

Joe 

If this post helps, then please Accept it as the solution

[Dylo_Bloomfield]

@JoeBarry just quickly I have noticed that the Earned Valuee line isn't going through to the 29/08/2023.

See Below.

What could be the error resulting in this?

Thanks Joe

[JoeBarry]

I think I know the error, can you change the part of the code from 

< = 'Calendar'[Current WE]  ),

to <= there is a space between them and it shouldn't be like this.

Also confirm that the CurrentWE date is 05/09/2023

Also check if the Cumulative forecast measure isn't covering the line. it may do if they have the same values. Just remove it from the visual for the moment.

Let me know if this helps

[Dylo_Bloomfield]

I have taken the spacing out from the formula and it is still the same. 

Hiding the Cumulative Forecast I can see it hasn't made it to the 29/08/2023.

Could it be the CurrentWE formula?

Thanks Joe

[JoeBarry]

What date do you have for the CurrentWE?

[Dylo_Bloomfield]

Currently showing the 29/08/2023.

Using the below formula.

CurrentWE = CALCULATE(MAX('Calendar (Milestone Source)'[Week Ending]), Filter('Calendar (Milestone Source)', 'Calendar (Milestone Source)'[Date] = TODAY()))

[JoeBarry]

Is your Calendar table a Date Table? Meaning it has a row for each date, not missing a day?

Could you provide me with a screenshot of the Calendar table with the date column and todays date, with the column Week Ending & CurrentWE visible?

I need to see how it's setup

[Dylo_Bloomfield]

Yes, see below. Thanks Joe.

[JoeBarry]

Ok, looking at how it's set up, based on this sturcture the CurrentWE column is correct. It will change to 05/09/2023 on Saturday. 

I checked again in my data and it works. Did you adapt the 

Cumulative EV Labor as I instructed?  There was a mistake change the below section to <=  there shouldn't be any space here

in my dataset the current end of the week is 03/09/2023 and it works 

[Dylo_Bloomfield]

Hi Joe,

Yes that has been corrected, see below my current formula.

Cumulative EV Labour S =

VAR MaxDate = MAX('Calendar (Milestone Source)'[Date])

RETURN

CALCULATE([Earned Labour],

KEEPFILTERS( 'Calendar (Milestone Source)'[Date] <= MaxDate && MaxDate <= 'Calendar (Milestone Source)'[CurrentWE]  ),

ALL('Calendar (Milestone Source)'))

It is odd that it isn't stopping. Unless the formula is slightly off. 

Thanks Joe. 

[JoeBarry]

The measure looks good. Not sure if it's possible for you to share a copy of the PBIX with me or an Excel/CSV with a copy of your data?

[Dylo_Bloomfield]

Joe we have figured it out on our end! The data from P6 was reading the incorrect Week Ending. 

I would like to thank you for all the help on achieving this solution. 

Have a good day 🙂 

[Dylo_Bloomfield]

Thanks Legend, Worked a Charm!!!