# DATESBETWEEN with start date unfiltered

#### ruthhacche

##### Board Regular
My current table is filtered for value by year so say start is 1/1/19 and end is 31/12/19 . I have a cumulative total that works below for me to total up the movements for the year selected. But I also want a cumulative total that includes transactions prior to the start date in the year selected. So I do not want the start date filtered to the currently selected date. Every time I try to do anything to the start date I get an error. Sure this is simple (but so am I).

Cumulative Total =
CALCULATE([Total],
DATESBETWEEN('Calendar'[Date],
MIN('Calendar'[Date]),
MAX('Calendar'[Date])
)
)

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### pjmorris

##### Well-known Member
without testing, but I would try something like this It should remove the filter that is currently applied, Min then returns the first date in the calendar and the Max the final, which is presumable what you want. I might have the format wrong, but the point is I'd be exploring the ALL function to remove the existing filter and then replace it with what I require.

Cumulative Total =
CALCULATE([Total],
ALL('Calendar', 'Calendar'[Date]),
DATESBETWEEN('Calendar'[Date],
MIN('Calendar'[Date]),
MAX('Calendar'[Date])
)
)

Hope this helps point you in the right direction.

#### ruthhacche

##### Board Regular
Thank you Peter. Trouble is I want the filter to stay on the MAX - just removed from the MIN. I am no doubt approaching this all wrong and should be using another measure all together. I will figure it out eventually. Have a nice weekend.

#### pjmorris

##### Well-known Member
I suspect that the Max will work out correctly anyway - though you could include a filter element of MAX('Calendar'[Date]) to reintroduce that element. - hopefully some one more proficient will clarify my idea for you. If I can I'll have another look at it.

Stay safe.

#### ruthhacche

##### Board Regular

Thanks. Will try in the morning.

#### ruthhacche

##### Board Regular
All I needed was some sleep it seems.

CF =
CALCULATE(
[Total],
FILTER(
ALL('Calendar'),
'Calendar'[FiscalYear] <= max ('Calendar'[FiscalYear])
)
)

#### pjmorris

##### Well-known Member
delighted to have helped, thanks for the feedback. Did the latter part need to be inside a FILTER or would it work like this?:

CF =
CALCULATE(
[Total],

ALL('Calendar'),
'Calendar'[FiscalYear] <= max ('Calendar'[FiscalYear])
)

Replies
0
Views
57
Replies
3
Views
97
Replies
0
Views
92
Replies
1
Views
100
Replies
3
Views
184