cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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

13 REPLIES 13
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

Frequent Visitor

@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

Solution Sage

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

Frequent Visitor

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

Solution Sage

What date do you have for the CurrentWE?

Frequent Visitor

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

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

Frequent Visitor

Yes, see below. Thanks Joe.

Solution Sage

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

Frequent Visitor

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

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?

Frequent Visitor

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 🙂

Frequent Visitor

Thanks Legend, Worked a Charm!!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors