cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamically Compare the Value of Two Periods

Scenario:  

We can use measures to have an easy comparison on the value of today and yesterday, current month and last month. However, it isn’t easy when we encounter below situations:

 

  1. If you need to compare the values of 2020 and last year at this time, but next time you need to compare the values of 2020 and 2018, you need to modify all measures that contain Year, or recreate these measures. When we have 100 measures, it will be a tedious work.
  2. If you need to compare  two periods that might have a different number of days, like comparing one month against a full year. Filter will be fixed in the formula and cannot be dynamically controlled by the slicer.

I will show you two ways to dynamically compare the value of two periods by slicer.

 

Detailed steps:

 

Use TREATAS

Sample data:

 Dedmon_1.png

Create two tables contain Date column for two period’s value and don’t create relationship between Date table and fact table:

Date1 = VALUES(Sales[OrderDate])

Date2 = VALUES(Sales[OrderDate])

Dedmon_2.png

 

Create the following measures(TREATAS applies the result of a table expression as filters to columns from an unrelated table):

SalesAmount = SUM(Sales[Sales])

Sales1 = CALCULATE([SalesAmount],KEEPFILTERS(TREATAS(VALUES(Date1[OrderDate]),Sales[OrderDate])))

Sales2 = CALCULATE([SalesAmount],KEEPFILTERS(TREATAS(VALUES(Date2[OrderDate]),Sales[OrderDate])))

Delta Sales = [Sales1]-[Sales2]

 

Compare value between 2013 Qtr4 and 2014Qtr1:

 Dedmon_3.png

 

Use USERELATIONSHIP:

Sample data

Dedmon_4.png

Create a new date table for comparison date and create an inactive relationship to Dim_Date:

Comparison Date = Dim_Date

Dedmon_5.png

Create the following measures:

SalesAmount = SUM(Sales[Sales])

ComparisonSalesAmount =
CALCULATE (
    SUM ( Sales[Sales] ),
    CALCULATETABLE (
        VALUES ( Dim_Date[Date] ),
        USERELATIONSHIP ( Dim_Date[Date], 'Comparison Date'[Date] ),
        REMOVEFILTERS ( Dim_Date[Date] )
    )//it will use filter from Comparison Date table instead of Dim_Date table by USERELATIONSHIP and REMOVEFILTERS
)

 

Create two slicers. One for Dim_Date and the other one for Comparison Date:

Compare values between 2013/4/1-2013/12/31 and 2014/4/1-2014/12/31

 Dedmon_6.png

 

 

 

Author: Dedmon 

Reviewer:  Kerry & Ula