Forum Discussion

67nmark's avatar
67nmark
Icon for Helper I rankHelper 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 da...
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

opticshrew's avatar
opticshrew
Icon for Resolver II rankResolver 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
    Icon for Helper I rankHelper 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.