Need to sum values with date prior to slicer month that is selected

[ShaelynFrench]

Hi,

I have a fact table that contains TransDate and StmnUnits and another linked table that contains StmnDate. I'm trying to sum all StmnUnits that have a TransDate prior to a certain date and also the StmnDate after that same date. My formula is:

HeldHoursCalc1 = CALCULATE (
	SUM( dbo_Transactions[StmnUnits] ),
	dbo_Matters[MatterType] = "Hourly",
	FILTER( dbo_Transactions, dbo_Transactions[TransDate] <= [As of Date] ),
	FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > [As of Date] )
)

This formula seems beholden to the month/year slicer and will only return values for the month selected, rather than all values prior to the [As of date].

PowerBI Result:

Desired Result:

Link to pbix file: https://drive.google.com/file/d/18TeRBVHJrgipdRPzMzXXad7EWL7igU_v/view?usp=sharing 

Thank you in advance!

[ShaelynFrench · Accepted solution]

I solved it! If anyone comes accross this later, the solution was to use the ALLEXCEPT function on the Transactions table.

HeldHoursCalc1 = 
var _max = maxx(allselected(Dates),Dates[Date])
return
CALCULATE (
	SUM( dbo_Transactions[StmnUnits] ),
	dbo_Matters[MatterType] = "Hourly",
	FILTER( ALLEXCEPT(dbo_Transactions, dbo_Professionals[ProfName]), dbo_Transactions[TransDate] <= _max ),
	FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > _max  )
)

[ShaelynFrench]

Hi @amitchandak  thank you for your response. I have a date table -- the [As of date] in my post above is 

As of Date = MAX( Dates[Date] ) so our formulas are functionally the same. It returns the same result. It is still only looking at dates in the chosen month/year and no dates prior. Thanks!

[amitchandak]

@ShaelynFrench , Here the filter has come from an independent sales table

HeldHoursCalc1 = 
//Date1 is an independent Date table, Date is joined with Table  
var _max = maxx(allselected(Date1),Date1[Date])
return 
CALCULATE (
	SUM( dbo_Transactions[StmnUnits] ),
	dbo_Matters[MatterType] = "Hourly",
	FILTER( dbo_Transactions, dbo_Transactions[TransDate] <= _max  ),
	FILTER( dbo_StmnLedger, dbo_StmnLedger[StmnDate] > _max  )
)

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI