Filter not working in DAX statement when Filtered by other Measure But working when working with var

[MrPaco]

I have a report with statusses of suppliers. This info is not delivered daily but irregularly. 

I created this measure to calculate the correct amount of lines, that works on Year level, month level or day level:

Test =

CALCULATE(

  SUM(Fact_Cables_Hist[DefinedInDB]),

  FILTER(Dim_Date, Dim_Date[Date] = [DateUsedHistory])

)

The DateUsedHistory is the measure that finds the last date of values recieved depend on the filter context: 

 DateUsedHistory =

CALCULATE(

    MAX(Fact_Cables_Hist[Date]),

    FILTER(Fact_Cables_Hist ,Fact_Cables_Hist[Date] <= [MaxDate])

) 

The 'Test' measure is working fine on day level, but when only months are shown, it takes all recieved that month into account.

 

When I do not use DateUsedHistory, but calculate it in the measure itself as a variable, the results are what I Expect.

 Defined Cables Actual =

VAR UseHis =

    CALCULATE(

      MAX(Fact_Cables_Hist[Date]),

      FILTER(Fact_Cables_Hist ,Fact_Cables_Hist[Date] <= [MaxDate])

    )

RETURN

    CALCULATE(

      SUM(Fact_Cables_Hist[DefinedInDB]),

      FILTER(Dim_Date, Dim_Date[Date] = UseHis)

)  

I already returned the value UseHis to check and it has the same value as DateUsedHistory.

The model is a simple one to many relation between Dim_Date and Fact_Cables_Hist.

Why is the filter working when is use a variable, but not when I use the measure to filter the Calendar table?

[johnt75 · Accepted solution]

FILTER is an iterator, so when you are using the [DateUsedHistory] measure within the filter of the Test measure, it is being evaluated for every row of the date table. When you store the value in a variable first it doesn't recalculate for every row, it just compares the already computed value with Dim_Date[Date]