Forum Discussion

Dylo_Bloomfield's avatar
Dylo_Bloomfield
Frequent Visitor
3 years ago
Solved

Stopping a Cumulative Earned Value Measure at a Certain Point

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 🙂

 

Need Help
  • JoeBarry's avatar
    JoeBarry
    3 years ago

     

    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

13 Replies

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

     

    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's avatar
      Dylo_Bloomfield
      Frequent Visitor
      3 years ago

      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's avatar
        JoeBarry
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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