Cumulative Cost Measure - Line Graph Stop Flat Lining Out

[mmccann875]

Hi there!

I have created a cumulative cost measure within one of my reports which is cumulating cost values over a 15 year period however I am not so happy with the visual which I will explain.

My data is split into 3 Cost Types (Legend) - Earned, Planned, To Go.

All three are showing fine on the line graph & 'Planned' will remain the same and show the trajectory over the 15 year span, however my value for 'Earned' is projecting until the current reporting month and then it is just flatlining as the cumulative value is remaining the same. I want this line to stop on the reporting month and when nothing further is cumulating to the 'Earned Value'. Also on the 'To Go' Value this works in the opposite and is only starting to project from the reporting month, and flatlining at 0 up until then so I would really want this line to not show until the reporting month and start projecting from there onwards. 

Any tips and suggestions would be greatly appreciated, below is the current DAX I am using to cumulate my values.

Cumulative Cost =

CALCULATE(

    SUM('EV Data Pivot'[Month Value]),

    FILTER(

        ALLSELECTED('EV Data Pivot'),

        'EV Data Pivot'[Month] <= MAX('EV Data Pivot'[Month])

    ),

    VALUES('EV Data Pivot'[Control Account]),

    VALUES('EV Data Pivot'[Cost Type])

)

-----

(Also may be worth noting I have replaced my date hierarchy on X Axis with a custom dateTable relationship for text values for FY, Quarter, Month to align with financial year reporting.)

[123abc]

It seems like you want to adjust your DAX measure to ensure that the 'Earned' line stops at the current reporting month and the 'To Go' line starts projecting from the reporting month onwards. You can achieve this by modifying your DAX measure to consider the current reporting month.

Here's how you can adjust your DAX measure:

Cumulative Cost =
VAR CurrentMonth = MAX('EV Data Pivot'[Month])
RETURN
CALCULATE(
SUM('EV Data Pivot'[Month Value]),
FILTER(
ALLSELECTED('EV Data Pivot'),
'EV Data Pivot'[Month] <= CurrentMonth
),
VALUES('EV Data Pivot'[Control Account]),
VALUES('EV Data Pivot'[Cost Type])
)

In this modified DAX measure, we define a variable CurrentMonth which holds the maximum month value in the 'EV Data Pivot' table. Then, we use this variable in the FILTER function to ensure that the calculation is performed only up to the current reporting month.

This adjustment should make the 'Earned' line stop at the current reporting month, as desired. Similarly, the 'To Go' line should start projecting from the reporting month onwards.

Make sure to replace 'EV Data Pivot'[Month] with your actual date column if it has a different name in your dataset. Also, ensure that your date table relationship is properly configured to align with your financial year reporting.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.