How to dynamically compare same period based on a slicer

Question

I would like to compare the same period of sessions per day. If i'm looking at Oct 10th 2018 to Oct. 16th 2018 (Wednesday to Tuesday), I would like to compare it to the same day range of last week:

+------+-------+-----+----------+-------------+--+
| year | month | day | sessions | last_period |  |
+------+-------+-----+----------+-------------+--+
| 2018 | oct   |  10 |     2000 |        2500 |  |
| 2018 | oct   |  11 |     2500 |        2400 |  |
| 2018 | oct   |  12 |     2600 |        2300 |  |
| 2018 | oct   |  13 |     2700 |        2450 |  |
| 2018 | oct   |  14 |     2400 |        2500 |  |
| 2018 | oct   |  15 |     2300 |        2200 |  |
| 2018 | oct   |  16 |     2000 |        1150 |  |
+------+-------+-----+----------+-------------+--+

A simple formula can make it work based on the 7-day interval:

same_last_period = CALCULATE(SUM(table[Sessions]),DATEADD(table[Date],-7,DAY))

but I would like the formula to depend on a date slicer. Say if i wanted to look at the Oct 1-Oct 20. I would like my formula to change and look at the same period right before with the same amount of day intervals. Ultimately this would be graphed as well.

Can you simply change -7 to a count of the selected days? Something like COUNTROWS(VALUES(table[Date])) or DISTINCTCOUNT(ALLSELECTED(table[Date]))? — Alexis Olson
it gives the last day's value (using the COUNTROWS function) DISTINCTCOUNT doesn't work as it only takes a column reference as an argument — Roger Steinberg

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-10T17:15:03

Try this:

same_last_period =
VAR DayCount = CALCULATE(DISTINCTCOUNT(table[Date]), ALLSELECTED(table[Date]))
RETURN CALCULATE(SUM(table[Sessions]), DATEADD(table[Date], -DayCount, DAY))

Edit:

This above doesn't work how I intended since you still have the year, month, and day in your filter context. That needs to be removed.

same_last_period =
VAR DayCount =
    CALCULATE (
        DISTINCTCOUNT ( 'table'[Date] ),
        ALLSELECTED ( 'table'[Date] ),
        ALLEXCEPT ( 'table', 'table'[Date] )
    )
RETURN
    CALCULATE (
        SUM ( 'table'[Sessions] ),
        DATEADD ( 'table'[Date], -DayCount, DAY ),
        ALLEXCEPT ( 'table', 'table'[Date] )
    )

The ALLEXCEPT removes any extra filter context except for Date.

How to dynamically compare same period based on a slicer

1 Answers