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...
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

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

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

Yes, see below. Thanks Joe.

 

  • Dylo_Bloomfield's avatar
    Dylo_Bloomfield
    Frequent Visitor
    3 years ago

    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 🙂 

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

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

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

    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?