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

user_10460447 0
Viewed 1757

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
dax powerbi powerquery sql
1 Answers

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 )
user_36791470
Related