Calculate Sum of a column before a specific date

[brookesmoore]

Hello,

I am trying to create a measure that calculates the sum of a column before a specific date.

Sample Data:

Revenue	Date
$500	1/5/2023
$1000	1/20/2023
$250	2/3/2023
$200	2/6/2023

I would like the measure to calculate the sum of the Revenue column up to 1/31/2023. I will then create a 2nd measure for the sum of revenue from 2/1/2023 and on.

I tried using the formulas below but end up with "Blank" in my card visual

=CALCULATE(SUM('Table1'[Revenue]), 'Table1'[Date] <= 1/31/2023))

=CALCULATE(SUM('Table1'[Revenue]), FILTER(ALL('Table1'), 'Table1'[Date] <= 1/31/2023))

Any help is appreciated. Thanks!

[Greg_Deckler · Accepted solution]

@brookesmoore Try:

SUMX(FILTER(ALLSELECTED('Table1'), 'Table1'[Date] <= DATE(2023,1,31)),[Revenue])

[brookesmoore]

Perfect. I used the DATE function with my previous formulas which worked, but was not allowing me to filter with a slicer on the page. I tried your formula and its working with the slicers now. Thank you thank you!