CODEWITHSUNDEEP
daxmeasures
Pedro Alves
Pedro Alves

Reputation: 1054

DAX - Calculate between two dates - Not getting results

I want to calculate the SUM(AMOUNT) between the beginning of the previous month (01-05-2018) and the DATEADD([Date];-1;MONTH) (21-05-2018). For that I'm using this:

CALCULATE (
    SUM(AMOUNT);
    FILTER (dataset; MAX(dataset[Date]) <= DATEADD(dataset[Date];-1;MONTH));
    FILTER (dataset; MIN(dataset[Date]) >= STARTOFMONTH(DATEADD(dataset[Date];-1;MONTH))))

But I'm getting 0 rows on chart with this measure. My dataset only have 2 coluns:

AMOUNT
Date

Can you resolve this issue?

Upvotes: 0

Views: 3646

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

I think you want your formula to look more like this:

CALCULATE (
    SUM([AMOUNT]);
    FILTER (dataset;
        dataset[Date] <= DATEADD(dataset[Date];-1;MONTH) &&
        dataset[Date] >= STARTOFMONTH(DATEADD(dataset[Date];-1;MONTH))))

Upvotes: 1

StelioK
StelioK

Reputation: 1781

Try this:

Total = calculate(sum(amount), datesinperiod(dataset[date], lastdate(dataset[date]), -1, Month))

This will give you 1 month back from the max date, I think this should be enough to get you started.

Upvotes: 0

Related Questions