Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Thank you 🙂
Solved! Go to 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
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.
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.
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.
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.
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
in my dataset the current end of the week is 03/09/2023 and it works
Hi Joe,
Yes that has been corrected, see below my current formula.
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!!!
User | Count |
---|---|
92 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
156 | |
145 | |
105 | |
72 | |
55 |