Forum Discussion
Running Total by month
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:
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]) ) )
6 Replies
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
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 ) )
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.
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]) ) )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]) ) )
