Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Relative filtering: Week before last week

Hi there

 

I have a time series i.e. a column of decimal numbers next to a column with dates. I would like a card that shows me four big numbers:

- the sum of the last seven days (e.g. 5 September 2018 - 11 September 2018)

- the sum of the previous seven days (e.g. 29 August  2018- 4 September 2018)

- the sum of the last seven days last year (e.g. 5 September 2017 - 11 September 2017)

- the sum of the previous seven days last year (e.g. 29 August  2017- 4 September 2017)

 

If I use a card with relative date filtering, I can do the last seven days but I don't know how to do the previous seven days or the same things last year.

 

I thought I would perhaps use a moving sum i.e. a measure that daily works out the sum of the previous seven days but even then, I wouldn't know how to pull the measure seven days ago.

 

Does anyone know how to resolve this? Thanks a lot for reading.

  • you can calculate the intervals yourself with DATESBETWEEN, e.g.

    Last7days = CALCULATE(SUM('Data'[Value]),DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY()))
    
    Previous7days = CALCULATE(SUM('Data'[Value]),DATESBETWEEN('Calendar'[Date],TODAY()-13,TODAY()-7) )
    
    Last7daysly = CALCULATE(SUM('Data'[Value]),SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY())))
    
    Previous7daysLY = CALCULATE(SUM('Data'[Value]),SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[Date],TODAY()-13,TODAY()-7)))

2 Replies

  • Stachu's avatar
    Stachu
    Community Champion

    you can calculate the intervals yourself with DATESBETWEEN, e.g.

    Last7days = CALCULATE(SUM('Data'[Value]),DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY()))
    
    Previous7days = CALCULATE(SUM('Data'[Value]),DATESBETWEEN('Calendar'[Date],TODAY()-13,TODAY()-7) )
    
    Last7daysly = CALCULATE(SUM('Data'[Value]),SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[Date],TODAY()-6,TODAY())))
    
    Previous7daysLY = CALCULATE(SUM('Data'[Value]),SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[Date],TODAY()-13,TODAY()-7)))
    • Anonymous's avatar
      Anonymous
      Not applicable

      Stachu

       

      Indeed! That is a good solution. Thank you very much