Referencing a measure in DAX DATESBTWEEN function

Question

In Powerpivot 2013 I am trying to calculate unit sales per day between the last time an item was received and the last time it was sold, but I keep getting tripped up by the DATESBETWEEN fuction.

These measures return the dates of of the last recieve and last sale:

Lastsale:=CALCULATE(LASTDATE(InvDetail[Date]), InvDetail[Type]="Sale") Lastrecv:=CALCULATE(LASTDATE(InvDetail[Date]), InvDetail[Type]="Receive", all(InvDetail[Date]))

This is what I'm using to get the units sold between the last receive and last sale, but it's not working. I don't get an error, just crazy big numbers.

UnitsSold:=calculate(sum(InvDetail[units]), InvDetail[Type]="Sale", DATESBETWEEN(InvDetail[Date], [Lastrecv], [Lastsale]))

If I hard-code in the dates as below, it works.

UnitsSold:=calculate(sum(InvDetail[units]), InvDetail[Type]="Sale", DATESBETWEEN(InvDetail[Date], date(2015,1,1), date(2015,2,1)))

I also have to get the number of dates in that date range too, but I haven't gone there yet.

Is there some reason why I can't use the measures in the DATESBETWEEN function?

Jacob Jacob · Accepted Answer · 2015-02-10T07:42:14

It's tricky without the data but my guess is that inside the DATESBETWEEN() you just aren't getting the expected filter context.

The first thing I would try is incorporating the measures in a CALCULATE() with a FILTER():

= CALCULATE(SUM(InvDetail[units]), 
     InvDetail[Type]="Sale", 
     FILTER(ALL(InvDetail[Date]), 
             InvDetail[Date] >= [Lastrecv] &&
             InvDetail[Date] <= [Lastsale]
            )
            )

You could adapt this to count your dates with a DISTINCTCOUNT() on the date column instead of summing the units.

Referencing a measure in DAX DATESBTWEEN function

1 Answers