Dynamically Compare the Value of Two Periods

[V-lianl-msft]

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:

 

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

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:

 

Use USERELATIONSHIP:

Sample data

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

Comparison Date = Dim_Date

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

 

Author: Dedmon 

Reviewer:  Kerry & Ula

Dynamically Compare the Value of Two Periods.pbix