cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
67nmark Regular Visitor
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.

Thanks in advance,
Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-jiascu-msft Super Contributor
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 )
    )

last n days2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
opticshrew Member
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
v-jiascu-msft Super Contributor
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 )
    )

last n days2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.
67nmark Regular Visitor
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.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 376 members 4,400 guests
Please welcome our newest community members: