Measure dax filter date

[David_Gonzalez]

I want to make a measure with filter but dont works:

Measure 1:  Max_Fecha = LASTDATE(loans[F_VALOR])

Measure final:   Spot1 = calculate(sum(loans[M_SALDO_AVG_DISPONIBLE_USD]), filter (loans, loans[F_VALOR] = [Max_Fecha]))

The result is the grand total and no filter nothing

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the F_Valor column to the Date column of the Calendar Table.  Try this measure

Spot1 = CALCULATE(sum(loans[M_SALDO_AVG_DISPONIBLE_USD]),LASTNONBLANK('Calendar'[Date],

CALCULATE(sum(loans[M_SALDO_AVG_DISPONIBLE_USD]))))

Hope this helps.

[David_Gonzalez]

Thx Ashish, I have the calendar table, and his measure works, but How can I add the filter the MaxDate? this is beacuse daily the data update wiht the previous date .

[Ashish_Mathur · Accepted solution]

Hi,

As long as the Calendar table has been created using the CALENDAR() function, the formula should always return the value of the last date in the Calendar Table (which in turn should pick up the last date from the Data Table).

Hope this helps.