Running Total by month

[ronaldbalza2023]

Hi everyone, how can I have a measure that is running total? I have a date table and visual that has a slicer of month and year.

The problem with my measure is that when I have ticked the slicer into the current month (say October), this gives me the amount on October only and does not count the previous month.

Table:

Measure:

Running Total = 
CALCULATE( 
	[Total Sales], 
	FILTER(
		ALLSELECTED( 'Date'),
		'Date'[Date] <= MAX( 'Date'[Date])
	)
)

Slicer:

[Jihwan_Kim]

Hi,

Instead of using ALLSELECTED, please try to use ALL and more conditions adding into the CALCULATE that are only calculating for the same year + less than or equal to the current date.

[ronaldbalza2023]

Hi @Jihwan_Kim , thanks for taking the time on this. I use the ALL instead of ALLSELECTED and it gave me a weird total. I also removed the filter slicer selection.

Running Total = 
CALCULATE( 
	[Total Sales], 
	FILTER(
		ALL( 'Date'),
		'Date'[Date] <= MAX( 'Date'[Date])
	)
)

[Jihwan_Kim · Accepted solution]

Hi, 

Sorry that only seeing the picture does not help me to write the accurate measure.

Please try to add the condition that shows the same year with the slicer selection.

For instance,

Running Total =

CALCULATE( [Total Sales],

FILTER( ALL( 'Date'),  yearcolumn = max(yearcolumn) &&  'Date'[Date] <= MAX( 'Date'[Date]) ) )

[ronaldbalza2023]

@Jihwan_Kim kamsahamnida Kim! Works like a charm 🙂

[amitchandak]

@ronaldbalza2023 , fi you select one month and want more than one month then you need an independent table

else this should work

Running Total = 
CALCULATE( 
	[Total Sales], 
	FILTER(
		ALL( 'Date'),
		'Date'[Date] <= MAX( 'Date'[Date])
	)
)

also refer

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[ronaldbalza2023]

Hi @amitchandak, thanks for taking the time on this. On your video link, I haven't done your suggestion yet with having two dates table. What I've done is I copied your datesinperiod dax and played it a little bit. However, it is not dynamic as it should be. The current slicer is for October which is showing the correct running total, however when I changed it to another month say September, it will not show the correct total. I think having two dates table is not a good idea.

Running Total = 
CALCULATE (
    [Total Sales)],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -4, MONTH )
)