Power BI - P1 - Calculate Change vs Difference for two Date Ranges

Colin Maitland, 26 July 2022

This is the first part of a two-part article in which I demonstrate how to structure the Power BI data model and write measures to calculate and display the change and the difference between two user selected sets of dates for a measure such as Actual Revenue for Won Opportunities.

The image below shows a report that displays a comparison of the change and the difference between Actual Revenue for two user-selected date ranges, e.g., Fiscal Q1/Q2 FY22 and Fiscal Q1/Q2 FY20.

1. The Fiscal Periods 1 (DR1) and Fiscal Periods 2 (DR2) slicers allow the user to select two different Fiscal Periods. DR1 and DR2 in this report are abbreviations for Date Range 1 and Date Range 2.

2. The Actual Revenue – Change visualisation shows the actual and percentage change in Actual Revenue from Fiscal Period 2 to Fiscal Period 1.

3. The Actual Revenue – Difference visualisation shows the actual and percentage difference in Actual Revenue between Fiscal Period 2 and Fiscal Period 1.

Calculating Change versus Difference

You can refer to this website for a description of change verses difference. With the example presented in this article in mind, the basic formulas for calculating these are …
(a) change is … DR1 - DR2.
(b) change as a percentage is … ( DR1 - DR2 / DR2) x 100.
(c) difference is ABS( DR1 - DR2 ).
(d) difference as a percentage is … DR1 - DR2 / ( DR1 + DR2 / 2 ) x 100.

The implementation of these calculations in Power BI requires the configuration of two related components, i.e., (a) the data model and (b) some measures. The implementation also requires additional business logic to be added to the measures to handle blank, negative, zero and positive values.

Data Model

The following image shows the data model that is used.

This data model has the following features.

1. Date is the primary Date dimension used to filter any Fact tables such as Opportunities. In this example, Date has one active and two inactive relationships from Date to Opportunities. Date is marked as a Date table in the data model.

2. ‘Date 2’ table is a secondary Date dimension used to filter the Date. It is not used to filter any Fact tables. The following are important aspects of the configuration of ‘Date 2’ …

(i) ‘Date 2’ must contain the same set of dates as Date. You can use a simple DAX formula such as Date 2 = 'Date' to add ‘Date 2’ to the data model. You then need to manually configure the various properties of ‘Date 2’ and its columns.

(ii) Marking ‘Date 2’ as a Date table is optional. This is because there is no intention to reference ‘Date 2’ from any DAX Date/Time Intelligence functions such as DATEADD() etc. However, a best practice approach would be to mark ‘Date 2’ as a Date table anyway.

(iii) ‘Date 2’ has a single 1-to-1 bi-directional filtering relationship with Date based on their DateKey columns. Importantly, this relationship is inactive. This ensures that any visualisations or measures that use Date are not automatically filtered by ‘Date 2’. However, this relationship can be activated by a measure when required. In fact, this relationship will be activated by two of the measures demonstrated in these articles.

(iv) Finally, ‘Date 2’ may be optionally hidden so that the user’s view of the data model is simplified. This also helps to eliminate ambiguity and confusion. Except, for the type of example demonstrated in this article, users should be using Date instead of ‘Date 2’ to configure the filters, visualisations and measures in their Power BI reports.

3. Opportunities is the fact table used for calculating Actual Revenue for Won Opportunities. In this example data model Opportunities are the combined, denormalised, Opportunity and Opportunity Product records from the source system. In addition, you will notice that there are three relationships from Date to Opportunities of which one is the active relationship. The important aspect of this configuration is that (a) ‘Date 2’ filters Date and (b) Date filters Opportunities.

Report Configuration

In the first image displayed by this article, there are two slicers and two corresponding Actual Revenue visualisations. The configuration of the slicers enables the user to select two different sets of dates for use by the measures displayed by the visualisations.

1. The Fiscal Periods 1 (DR1) slicer is based on Date and displays an expandable Fiscal Period Hierarchy.

2. The Fiscal Periods 2 (DR2) slicer is based on ‘Date 2’ and displays an expandable Fiscal Period Hierarchy. It is because this slicer is based on a different Date table that the user can select a different set of dates from those selected using the Fiscal Periods 1 (DR1) slicer.

3. There is no need to edit the Visual Interactions between the slicers because the relationship between Date and ‘Date 2’ is inactive and because ‘Date 2’ does not have any relationships with any other tables in the data model. Therefore, changes to the selection of the dates displayed by either slicer will not impact each other.

4. The Actual Revenue visualisations display the measures that calculate the Actual Revenue and the calculated change and difference between the Actual Revenue for the two user selected sets of dates.

5. The Actual Revenue visualisations may be optionally configured to include one or more columns from Date, such as the Fiscal Quarter/Year column, but they should not include any columns from the ‘Date 2’.

Alternate Configuration

In the following image, the configuration has been changed so that the Actual Revenue – Fiscal Periods 1 (DR1) visualisation is filtered by the dates selected using the Fiscal Periods 1 (DR1) slicer and the Actual Revenue – Fiscal Periods 2 (DR2) visualisation is filtered by the dates selected using the Fiscal Period 2 (DR2) slicer.

This configuration differs to the configuration described previously in the following areas:

1. The data model has been changed so that the relationship between ‘Date 2’ to ‘Date is active rather than inactive.

2. The Visual Interactions on the report page are configured so that the Fiscal Period 1 (DR1) slicer, based on Date, does not filter the Fiscal Period 2 (DR2) slicer or related visualisation and the Fiscal Period 2 (DR2) slicer, based on ‘Date 2’, does not filter the Fiscal Period 1 (DR1) slicer or related visualisation. In addition, the two visualisations displayed by the report do not filter the slicers or each other.

Compared with the previously described Report Configuration, this Alternate Configuration does not support the calculation and display of Actual Revenue or the change and difference between Actual Revenue for the two user selected sets of dates on the same visualisation. This configuration also requires more care to be taken with the configuration of the Visual Interactions.

Conclusion

In this article, I have demonstrated the configuration of the data model and a report page in Power BI that allows the change and difference between two user selected sets of dates for a measure to be calculated and displayed.

In part-two of this article, I will demonstrate the formulas used for the measures for calculating change and difference. These measures include additional business logic to handle blank, negative, zero and positive values.