cancel
Showing results for
Did you mean:
Regular Visitor

## last n days

Hello,

I am trying to create a DAX formula that will calculate my total goods sold in the last n days.

The formula below gets me the correct result - using the date function with a hard coded date:
Sales Last 5 days = CALCULATE(SUM(Sales[Qty Sold]),FILTER(Sales,Sales[Date]>=date(2017,3,11)))

The problem starts when I try and replace the date function above with a measure. Something like:
Last5Days = lastdate(Sales[Date])-5). This measure returns the correct date and displays the date correctly in a card visualisation.

When I try the measure as part of a similar expression, the result is not correct - it returns the total qty sold across the whole dataset:
Last 5 days = CALCULATE(SUM(Sales[Qty Sold]),FILTER(Sales,Sales[Date]>=lastdate(Sales[Date])-5))

Is anyone able to help me both why the measure doesn't work and what I need to change to make it work.

Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super Contributor

## Re: last n days

@67nmark

Hi Mark,

It is the context that makes the result right or wrong. When you have a context such as dates. You could try this formula to have a test.

```Last 5 days 1 =
CALCULATE (
SUM ( Sales[Qty Sold] ),
DATESINPERIOD ( 'Sales'[Date], MIN ( Sales[Date] ), -5, DAY )
)```

If you want to use your formula in a card visualization, you need to make a little change. It's still the context that makes the tricks. The card visualization takes the whole dataset as context.

```Last 5 days 2 =
VAR L5D =
LASTDATE ( Sales[Date] ) - 5
RETURN
CALCULATE (
SUM ( Sales[Qty Sold] ),
FILTER ( ALL ( Sales ), Sales[Date] > L5D )
)```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Member

## Re: last n days

Hi Mark,

Have you tried something like this:

Sales Last 5 days = Calculate ( Sum ( Sales[Qty Sold]) , DateDiff ( Sales[Date] , Now() , DAY ) < 5 )

Alternately you could create a calculated column as follows and then add this in as a visual level filter:

Is Last 5 days = If ( DateDiff ( Sales[Date] , Now() , DAY ) < 5 , "Yes" , "No" )

Thanks,

J

Highlighted
Super Contributor

## Re: last n days

@67nmark

Hi Mark,

It is the context that makes the result right or wrong. When you have a context such as dates. You could try this formula to have a test.

```Last 5 days 1 =
CALCULATE (
SUM ( Sales[Qty Sold] ),
DATESINPERIOD ( 'Sales'[Date], MIN ( Sales[Date] ), -5, DAY )
)```

If you want to use your formula in a card visualization, you need to make a little change. It's still the context that makes the tricks. The card visualization takes the whole dataset as context.

```Last 5 days 2 =
VAR L5D =
LASTDATE ( Sales[Date] ) - 5
RETURN
CALCULATE (
SUM ( Sales[Qty Sold] ),
FILTER ( ALL ( Sales ), Sales[Date] > L5D )
)```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: last n days

Thanks, I couldn't get your Sales Last 5 days measure to produce the result - it was doing the same as my measure and seemingly not recognising the date.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 376 members 4,400 guests
Recent signins: