Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Dylo_Bloomfield
Frequent Visitor

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.

Dylo_Bloomfield_0-1693458092432.png

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 🙂

 

1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

 

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

View solution in original post

13 REPLIES 13
JoeBarry
Solution Sage
Solution Sage

 

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 just quickly I have noticed that the Earned Valuee line isn't going through to the 29/08/2023.

See Below.

Dylo_Bloomfield_0-1693468389157.png

What could be the error resulting in this?

 

Thanks Joe

 

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

 

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.

Dylo_Bloomfield_0-1693469451218.png

Could it be the CurrentWE formula?

 

Thanks Joe

What date do you have for the CurrentWE?

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()))

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

Yes, see below. Thanks Joe.

Dylo_Bloomfield_0-1693471321501.png

 

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

JoeBarry_0-1693472775181.png

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

JoeBarry_1-1693472878243.png

 

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. 

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?

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 🙂 

Thanks Legend, Worked a Charm!!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.