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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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