cancel
Showing results for
Did you mean:
cassidy4 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

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

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

Re: Show Changes between two given periods

@cassidy4 you can DAX function sameperiodlastyear

Proud to be a Datanaut! Kudos Help Connect with me on Linkedin. Feel free to email me with any of your BI needs. 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) )`  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

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.