Forum Discussion

67nmark's avatar
67nmark
Helper I
9 years ago
Solved

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

  • opticshrew's avatar
    opticshrew
    Resolver II
    9 years ago

    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

    • 67nmark's avatar
      67nmark
      Helper I
      9 years ago

      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.