Date filter not working as expected

[ShaelynFrench]

Hi,

I have a fact table that contains all of the columns: StmnUnits, TransDate and StmnDate. I want to sum StmnUnits if the TransDate is before or equal to the last date of the month that is being viewed (via Month & Year slicer) and the StmnDate is after the last day of the month that is being viewed ( Max(Dates[Date]) ). This is my measure formula:

Held Hours (YTD) = CALCULATE (

    SUM( Transactions[StmnUnits] ),

    Transactions[Matters(Matters).MatterType] = "Hourly",

    FILTER( Transactions, Transactions[TransDate] <= MAX( Dates[Date] ) ),

    FILTER( Transactions, Transactions[StmnLedger(StmnLedger).StmnDate] > MAX( Dates[Date] ) )

)

This formula only sums if the TransDate is in the month that is being viewed via Month & Year slicer, but I want to include dates prior as well. Please help! Thanks!

[lukiz84]

Hi,

- is Dates[Date] connected to the Transactions table? If so, to which field?

- Don't do 2 filters, just combine them

FILTER(
   Transactions,
   Transactions[TransDate] <= MAX( Dates[Date] &&
   Transactions[StmnDate] > MAX(Dates[Date])
)

[ShaelynFrench]

Hi,

Thank you for your reply. There is an active relationship between Transactions[TransDate] and Dates[Date] and an inactive relationship between Transactions[StmnDate] and Dates[Date].

[lukiz84]

Ok, when you have this relation between TransDate and Date then you need to write:

VAR MaxSelectedDate = MAX(Dates[Date])

FILTER(
   ALLSELECTED(Transactions),
   Transactions[TransDate] <= MaxSelectedDate &&
   Transactions[StmnDate] > MaxSelectedDate)
)