Forum Discussion

jal1aaa's avatar
jal1aaa
Frequent Visitor
5 years ago
Solved

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

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 TeamForecastForecast Date
Northeast1000Jan-21
Southeast1200Jan-21
Midwest800Jan-21
Northwest900Jan-21
Southwest700Jan-21
Northeast950Feb-21
Southeast1300Feb-21
Midwest850Feb-21
Northwest950Feb-21
Southwest750Feb-21
Northeast950Mar-21
Southeast1250Mar-21
Midwest875Mar-21
Northwest900Mar-21
Southwest750Mar-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 1Forecast date 2  
21-Feb21-Mar  
    
Sales TeamForecast 1Forecast 2Change
Southeast13001250-50
Midwest85087525
Northwest950900-50
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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