Filtering based on date

[Torbenan]

Hi
i trying to calculate the amount that has been invoice during the last year. Since the table

Sales'[TotalInvoiceAmount] isn't update daily, I need to find the latest invoice date and

substrat 365 days. This is my formular:

Last12Month invoiced = CALCULATE(

    SUM('Sales'[TotalInvoiceAmount]);

FILTER(Sales; Sales[InvoiceDate].[Date] > LASTDATE(Sales[InvoiceDate].[Date]) - 365

))

No filtering is taking place, so what am I doing wrong. If I replace

"LASTDATE(Sales[InvoiceDate].[Date]) - 365" with "today() - 365", filtering works perfect.

[Anonymous]

Hi @Torbenan ,

Are you using your measure in a visual that is using the InvoiceDate column? LASTDATE() will evaluate the last date based upon the current context whereas TODAY() does not take the context into account of course..

You could try calculating the LASTDATE in a variable beforehand using an ALL statement to remove any context filters from your date column?

Best,

Kris

[calerof · Accepted solution]

Hi @Torbenan ,

Try using a variable like below:

Last12Month invoiced = VAR LastInvDate = LASTDATE( Sales[Date] )
                       RETURN
                       CALCULATE(
                             SUM(Sales[Sales]),
                             Sales[Date] > LastInvDate - 365
                       )

Hope it helps.

Cheers,

Fernando

[Torbenan]

This did the trick

Last12Monthinvoiced = VAR LastInvDate = LASTDATE( Sales[InvoiceDate] )

RETURN

CALCULATE(

SUM(Sales[TotalInvoiceAmount]);

Sales[InvoiceDate]> LastInvDate - 365

)