DATESBETWEEN with start date unfiltered

[ruthhacche]

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

 

[pjmorris]

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]

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]

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]

Thanks. Will try in the morning.

 

[ruthhacche]

All I needed was some sleep it seems.

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

 

[pjmorris]

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