Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
67nmark
Helper I
Helper I

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
v-jiascu-msft
Employee
Employee

@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.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
Employee

@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.
opticshrew
Resolver II
Resolver II

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.