CODEWITHSUNDEEP
sqlpowerbidaxpowerquery
Moun
Moun

Reputation: 315

Power BI DAX, Sum column with filters for only last date

I've a table 'invoice' of amount with different Dates, i want to sum the amount for the max date. For example if the max date is 25/03/2022, i'll sum only the rows of this date.

last_month_amount = calculate(SUM(invoice[amount]),FILTER(invoice, invoice[date] = LASTDATE('invoice'[date])))

but it calculate all the amounts without any filter.

in sql i want this:

select sum(invoice.amount)
from invoice
where invoice.date = (select max(date) from invoice)

but i got this

select sum(invoice.amount)
from invoice

Upvotes: 2

Views: 8235

Answers (1)

RADO
RADO

Reputation: 8148

There are two errors in your formula:

  1. You are using LASTDATE function on the invoice table, but it should be used on a proper calendar table (it expects continuous dates);
  2. You placed LASTDATE inside the CALCULATE and FILTER functions. As a result, it computes the last date within the filter context of these functions (that is, it calculates the last date for each record instead of the last date for the entire table).

To solve the problem, simply store the last date in a variable:

last_month_amount =
VAR Latest_Date = MAX ( 'invoice'[date] )
RETURN
    CALCULATE ( SUM ( invoice[amount] ), 'invoice'[date] = Latest_Date )

Upvotes: 6

Related Questions