RunningTotal to stop at max Date

[Callmeadel]

Good afternoon,

I was just wondering what can of dax formula can I use to show the max date of my line

the orange line is Actual transaction amount and blue line is the Estimated amount problem is I dont want the orange line to keep going the max date should be may. 

Thank you!

[daXtreme · Accepted solution]

Here it is how to do what you want.

Navigate to this page:

Standard time-related calculations – DAX Patterns

and then to this header:

Controlling the visualization in future dates

This shows exactly how to deal with such issues.

[v-zhangti · Accepted solution]

Hi, @Callmeadel 

You can try the following methods.

Measure:

RunningTotal =
IF (
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) <= MONTH ( TODAY () ),
    CALCULATE (
        SUM ( 'Table'[Actual transaction] ),
        FILTER ( ALL ( 'Table' ), [Date] <= SELECTEDVALUE ( 'Table'[Date] ) )
    ),
    BLANK ()
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Callmeadel]

Thank you zhangti, I will test your method out

This is a method I used and worked great

ActivateDates =

var maxActiveDates= MAX('TR-Combined'[Date])

var minactivedates= MIN('TR-Combined'[Date])

return

SWITCH(

true(),

DateCombined[Date]>= minactivedates&&

DateCombined[Date]<=maxActiveDates,1,0)

Cumulative Transcaction =

SWITCH(

true(),

Max(DateCombined[ActivateDates])=1

CALCULATE(

sum('TR-Combined'[Spent]),

all(DateCombined[Date]),

DateCombined[Date]<=MAX(DateCombined[Date])))

[daXtreme · Accepted solution]

Here it is how to do what you want.

Navigate to this page:

Standard time-related calculations – DAX Patterns

and then to this header:

Controlling the visualization in future dates

This shows exactly how to deal with such issues.

[Callmeadel]

Thank you for the reply, I have created that measure it did say we can't use it on the report so how can it be used to fitler the graph? Do I drag it to filters because it won't let me add it to values.

[Callmeadel]

They wanted me to use it in a column and then use a slicer that makes sense, but my table doesnt have any data after may so everything is returning True which still keeps the line 😞