DATESBETWEEN with start date unfiltered

ruthhacche

Board Regular
Joined
Sep 22, 2017
Messages
73
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])
)
)
 

Some videos you may like

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
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 22, 2017
Messages
73
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
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 22, 2017
Messages
73

ADVERTISEMENT

Thanks. Will try in the morning.
 

ruthhacche

Board Regular
Joined
Sep 22, 2017
Messages
73
All I needed was some sleep it seems.

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

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
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])
)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,202
Members
412,706
Latest member
msousa25
Top