CODEWITHSUNDEEP
powerbidax
Roger Steinberg
Roger Steinberg

Reputation: 1604

How to dynamically compare same period based on a slicer

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.

Upvotes: 0

Views: 567

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Result

Upvotes: 1

Related Questions