Get Latest Accumulated Value of the month in Chart

[ainnossro]

Hi guys, I am relatively new in developing PBI report and also here in this forum. I will describe my question below, and if there is a topic related that already discuss about this, please let me know. I have tried to search high and low for the same situation I have but couldn't find one.

My data consist of many other value but my focus is on accumulated Total FTE. I would like to show the latest accumulated Total FTE for that month in a graph. As below screenshot shown, if I choose Consultant L1 as career level, the graph should shows the latest value in the month as shown in the table:
1 - Apr = 55.2

2 - May = 56.2

3 - Jun = 58.2

4 - Jul = 61.2

5 - Aug = 60.2 etc

Right now in the graph it shows sum value of the Total FTE of the month. Kindly advise and thanks in advance for any help and lead!

https://drive.google.com/file/d/1cE0fAStRx_e8E-ya3mjtwBhJqrDLnB9e/view?usp=drive_link

Regards,

Anne

[v-shex-msft]

Hi @ainnossro ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[ainnossro]

Thank you for the reminder, apologize for the delay as I continue on another project which only now I have the time to do this specific project, I have accepted one of the suggestion as solution, thanks!

[Dangar332 · Accepted solution]

Hi, @ainnossro 

try below code and check image 

new FTE = 
var a = CALCULATE(MAX(MY[Weekly End Date]),ALLEXCEPT(MY,'MY'[Fiscal Period]))
return 
maxX(FILTER(MY,MY[Weekly End Date]=a),MY[Total FTE])

[ainnossro]

Hey this is perfect for me, thanks!

[FreemanZ]

hi @ainnossro ,

access to your file was denied. Could you handle that?

[ainnossro]

I have granted all with file link to get Viewer access, thanks!

[FreemanZ]

hi @ainnossro ,

try to plot a measure like:

Measure = 
AVERAGEX(
    VALUES(MY[Staff.Name]),
    CALCULATE(AVERAGE(MY[Total FTE]))
) 

it worked like:

[ainnossro]

The problem using average is it does not capture the right accumulated FTE by end of the month, for example for Partner below, for July the value should be 61.20, but when we use average it becomes 60.45. but at least I have new measure to play around with, thank you so much for your feedback!

[ainnossro]

I have granted for xjtutjx already, is that you?