Running Total wi

[stackover]

Hi all. 

I need help with a DAX formula. I searched and tried a lot but I wan not able to create such measure yet.

I need to SUM "value" column where the date in the "Actual" column is the lastest possible, but I must not leave any project aside. If the lastest data in the "Actual" column was a year ago, it must be added too.

This is my DAX, but is not considering the the Project A because the value in the "Actual" column is not the MAX.

MH-Baseline = 
VAR mdp = MAX ('Project_Baseline'[Date])
VAR mda = MAX ('Project_Baseline'[Actual])

RETURN CALCULATE ( 
    SUM(Project_Baseline[Value]),
    'Date_List'[Date] <= mdp,
    Project_Baseline[Actual] = mda    
)

This is my database as example.

The running total should be the SUM of all data in yellow. 

[stackover · Accepted solution]

Never Mind. I was able to solve the problem using the DAX below.

Value running total in Date = 
VAR _MaxDateInID = CALCULATE(MAX('Project_Baseline'[Actual]),  ALLEXCEPT('Project_Baseline',Project_Baseline[Project ID]))
return CALCULATE(
	SUMX('Project_Baseline',[m1]),
	FILTER(
		ALLSELECTED('Project_Baseline'[Date]),
		ISONORAFTER('Project_Baseline'[Date], MAX('Project_Baseline'[Date]), DESC)
	)
)

[stackover]

Thanks @Ahmedx and @Anonymous but it is looking weird in the graph. I put my real data table in the .pbix file and the graph does not show a crescent line like in a running total line. As I don't have any negative number, it should be a crescente line, like and near to the orange line as this image below.

Here is the link to the pbix file.

https://we.tl/t-lpyUCADjtt

[Anonymous]

Hi @stackover 

@Ahmedx  GOOD ANSWER!

You can also try the following.

Two measures:

maxDate = CALCULATE(MAX([Actual]), ALLEXCEPT(Project_Baseline, Project_Baseline[Project ID]))

MH-Baseline = CALCULATE(SUM(Project_Baseline[Value]), FILTER(Project_Baseline, [Actual] = [maxDate]))

Best Regards,
Yulia Xu

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

[Ahmedx]

or try this

[Ahmedx]

pls try this