topic Re: RunningTotal to stop at max Date in DAX Commands and Tips

RunningTotal to stop at max Date

Callmeadel — Mon, 23 May 2022 21:43:35 GMT

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!

Re: RunningTotal to stop at max Date

daXtreme — Mon, 23 May 2022 23:01:48 GMT

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.

Re: RunningTotal to stop at max Date

Callmeadel — Tue, 24 May 2022 14:29:47 GMT

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.

Re: RunningTotal to stop at max Date

Callmeadel — Tue, 24 May 2022 14:59:15 GMT

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 😞

Re: RunningTotal to stop at max Date

v-zhangti — Thu, 26 May 2022 05:41:28 GMT

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.

Re: RunningTotal to stop at max Date

Callmeadel — Thu, 26 May 2022 14:14:40 GMT

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