Show Changes between two given periods

[cassidy4]

I'm trying to calculate the percent change between two given periods based on the dates selected in the date filter.

For instance, I want to calculate the % change for spend when the date filter is set to 10/21/2018 - 11/3/2018 (14 days). I would want to compare that spend to the spend that occurred between 10/7/2018 - 10/20/2018 (previous 14 days)

Thank you!

[v-yulgu-msft · Accepted solution]

Hi @cassidy4,

Based on my assumption, I tested with below sample data. The right image shows a date dimention table created by:

calendar = CALENDAR(DATE(2018,10,1),DATE(2018,11,30) )

There is no relationship between tables. Add 'Calendar'[Date] into slicer. Then, create measures:

Sum1 =
CALCULATE (
    SUM ( 'Table1'[Sales] ),
    FILTER (
        'Table1',
        'Table1'[Date] >= MIN ( 'calendar'[Date] )
            && 'Table1'[Date] <= MAX ( 'calendar'[Date] )
    )
)

Sum2 =
VAR countdays =
    DATEDIFF ( MIN ( 'calendar'[Date] ), MAX ( 'calendar'[Date] ), DAY )
VAR startdate =
    MIN ( 'calendar'[Date] ) - 1
        - countdays
VAR enddate =
    MIN ( 'calendar'[Date] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table1'[Sales] ),
        FILTER (
            'Table1',
            'Table1'[Date]
                <= MIN ( 'calendar'[Date] ) - 1
                && 'Table1'[Date]
                    >= MIN ( 'calendar'[Date] ) - 1
                        - countdays
        )
    )

change = ([Sum1]-[Sum2])/[Sum1]

Best regards,

Yuliana Gu

[parry2k]

@cassidy4 you can DAX function sameperiodlastyear