cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rahulp Frequent Visitor
Frequent Visitor

Calculating difference between two values in one column based on date and business line

Dear Community,

 

I have the following (excel) dataset where I would like to calculate the difference of the Actual column between months and per business line. I am fairly new to Power BI, therefore, given the way the data is structured, I still can't figure out a way to create a column/measure to get the desired output Difference.  In the Actual column, data is only filled for the months from January to August, while the remaining months are blank for now and will be updated in the excel over time.

 

DataCapture.PNG

 

I hope you can help with answering this question and thanks a lot for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Calculating difference between two values in one column based on date and business line

Hi @rahulp

First of all, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Try this for a new calculated column in your table. Haven't tested it for lack of data in the proper format: 

Difference =
VAR PreviousDate_ =
    CALCULATE (
        MAX ( Table1[Month] ),
        Table1[Month] < EARLIER ( Table1[Month] ),
        ALLEXCEPT ( Table1, Table1[Business Line] )
    )
VAR PreviousValue_ =
    CALCULATE (
        DISTINCT ( Table1[Actual] ),
        Table1[Month] = PreviousDate_,
        ALLEXCEPT ( Table1, Table1[Business Line] )
    )
VAR CurrentValue_ = Table1[Actual]
RETURN
    IF (
        NOT ISBLANK ( CurrentValue_ ) && NOT ISBLANK ( PreviousValue_ ),
        CurrentValue_ - PreviousValue_
    )

 

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

Re: Calculating difference between two values in one column based on date and business line

Hi @rahulp

First of all, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Try this for a new calculated column in your table. Haven't tested it for lack of data in the proper format: 

Difference =
VAR PreviousDate_ =
    CALCULATE (
        MAX ( Table1[Month] ),
        Table1[Month] < EARLIER ( Table1[Month] ),
        ALLEXCEPT ( Table1, Table1[Business Line] )
    )
VAR PreviousValue_ =
    CALCULATE (
        DISTINCT ( Table1[Actual] ),
        Table1[Month] = PreviousDate_,
        ALLEXCEPT ( Table1, Table1[Business Line] )
    )
VAR CurrentValue_ = Table1[Actual]
RETURN
    IF (
        NOT ISBLANK ( CurrentValue_ ) && NOT ISBLANK ( PreviousValue_ ),
        CurrentValue_ - PreviousValue_
    )

 

View solution in original post

rahulp Frequent Visitor
Frequent Visitor

Re: Calculating difference between two values in one column based on date and business line

Thank you so much @AlB , the solution worked! Apologies for not including the sample data in the correct format, please find this below.

 

Business LineMonthActual
A01/01/201910
A01/02/201920
A01/03/201940
A01/04/201970
A01/05/2019100
A01/06/2019120
A01/07/2019130
A01/08/2019150
A01/09/2019 
A01/10/2019 
A01/11/2019 
A01/12/2019 
B01/01/20195
B01/02/20197
B01/03/20199
B01/04/20198
B01/05/201910
B01/06/201911
B01/07/201915
B01/08/201918
B01/09/2019 
B01/10/2019 
B01/11/2019 
B01/12/2019 
C01/01/2019100
C01/02/201980
C01/03/201965
C01/04/201950
C01/05/201945
C01/06/201940
C01/07/201935
C01/08/201925
C01/09/2019 
C01/10/2019 
C01/11/2019 
C01/12/2019 
AtlasCBG New Member
New Member

Re: Calculating difference between two values in one column based on date and business line

Any example report with this calculation you can share im triying to follow the isntruccion but i can not get it 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)