Forum Discussion

stackover's avatar
stackover
Frequent Visitor
2 years ago
Solved

Running Total wi

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. 

Need Help
  • stackover's avatar
    stackover
    2 years ago

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

5 Replies

  • stackover's avatar
    stackover
    Frequent Visitor
    2 years ago

    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)
	)
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • stackover's avatar
    stackover
    Frequent Visitor
    2 years ago

    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