cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cassidy4 Frequent Visitor
Frequent Visitor

Show Changes between two given periods

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Show Changes between two given periods

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) )

1.PNG2.PNG

 

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]

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: Show Changes between two given periods

@cassidy4 you can DAX function sameperiodlastyear

 





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Community Support Team
Community Support Team

Re: Show Changes between two given periods

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) )

1.PNG2.PNG

 

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]

3.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.