How to calculate the difference in values between two data rows in the same table

[jal1aaa]

I have a table with data for multiple periods and want to calculate the difference between two periods based on user selection. For example, I have a table where different sales teams update their forecasted sales each month

Sales Team	Forecast	Forecast Date
Northeast	1000	Jan-21
Southeast	1200	Jan-21
Midwest	800	Jan-21
Northwest	900	Jan-21
Southwest	700	Jan-21
Northeast	950	Feb-21
Southeast	1300	Feb-21
Midwest	850	Feb-21
Northwest	950	Feb-21
Southwest	750	Feb-21
Northeast	950	Mar-21
Southeast	1250	Mar-21
Midwest	875	Mar-21
Northwest	900	Mar-21
Southwest	750	Mar-21

I would like to create a table where the user selects two forecast dates and the table shows the difference between those two forecasts and ONLY includes records that have a difference.  For example, the output could select Mar 21 and Feb 21 and get the following output

Forecast date 1	Forecast date 2
21-Feb	21-Mar
Sales Team	Forecast 1	Forecast 2	Change
Southeast	1300	1250	-50
Midwest	850	875	25
Northwest	950	900	-50

[Anonymous · Accepted solution]

Hi @jal1aaa ,

I created a sample pbix file for you, please check whether that is what you want. Please find the attachment for more details.

1. Create two dimension table: Forecast date 1 table and Forecast date 2 table using below formula and create two date slicers base on the date field in these two tables

Forecast date 1 = VALUES('Table'[Forecast Date])

Forecast date 2 = VALUES('Table'[Forecast Date])

2. Create the below measures to get the forecast1, forecast2 and change

Forecast 1 = 
CALCULATE (
    SUM ( 'Table'[Forecast] ),
    FILTER (
        'Table',
        'Table'[Forecast Date] = SELECTEDVALUE ( 'Forecast date 1'[Forecast Date] )
    )
)

Forecast 2 = 
CALCULATE (
    SUM ( 'Table'[Forecast] ),
    FILTER (
        'Table',
        'Table'[Forecast Date] = SELECTEDVALUE ( 'Forecast date 2'[Forecast Date] )
    )
)

Change = [Forecast 1]-[Forecast 2]

3. Create a table visual and apply the visual level filter with the condition: Change is not 0 just as shown in below screenshot

Best Regards

[parry2k]

@jal1aaa solution is attached, you can tweak it as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡