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

[Anonymous]

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.

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

[AlB · Accepted solution]

Hi @Anonymous

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

[makimakiato]

Thank you! I have been looking for this approach. I've used it to calculate first derivative. 

[AtlasCBG]

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

[Anonymous]

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

Business Line	Month	Actual
A	01/01/2019	10
A	01/02/2019	20
A	01/03/2019	40
A	01/04/2019	70
A	01/05/2019	100
A	01/06/2019	120
A	01/07/2019	130
A	01/08/2019	150
A	01/09/2019
A	01/10/2019
A	01/11/2019
A	01/12/2019
B	01/01/2019	5
B	01/02/2019	7
B	01/03/2019	9
B	01/04/2019	8
B	01/05/2019	10
B	01/06/2019	11
B	01/07/2019	15
B	01/08/2019	18
B	01/09/2019
B	01/10/2019
B	01/11/2019
B	01/12/2019
C	01/01/2019	100
C	01/02/2019	80
C	01/03/2019	65
C	01/04/2019	50
C	01/05/2019	45
C	01/06/2019	40
C	01/07/2019	35
C	01/08/2019	25
C	01/09/2019
C	01/10/2019
C	01/11/2019
C	01/12/2019

[ripstaur]

So this looked like it might work for my own situation, and I tried it. Of course, just as with anything in this environment, copying a solution is a dangerous thing, especially for those of us who don't understand what the solution is doing. If you are a person who is happy that the solution worked and could care less about what's "under the hood," then this type of solution is good for you. 

For those of us who are not DAX or PowerQuery wizards, it would be helpful to provide a couple of notes to let us know what some of these functions do. Why, for instance, is the DISTINCT function needed; and what does the ALLEXCEPT function do? I ask because I tried this solution and as soon as I hit the ENTER key, I got an error "A table of multiple values was supplied where a single value was expected." Because I only partially understand what this code was supposed to do, I have no idea how to begin troubleshooting much less fixing the problem. The only real difference I can see between the structure of the data I am using (GitHub COVID-19 Confirmed Case time series data) and the example given is that in my data, there are no blank cells in the value column ("Cases" in my dataset, "Actuals" in the example given here). Other than that, I think everything is about the same. I have a column of dates, a column of locations (Counties/locations encoded by using FIPS codes) that fulfills the same function as the "Business Line" function in the example, and a column of numeric values I want to calculate differences for. 

I am trying to get PowerBI to calculate the difference between the case count for a specific location on one date and the case count for the same location on the day before - essentially, "New Cases." Can anyone help? I can provide a link to the dataset if needed...https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/... (hint: to bring this into PowerBI, you can go to a Web datasource, navigate to this page, right-click the "Download" link, copy the link address, and paste it into the box in the PowerBI dialog). 

[Batman501]

Hey, any luck on solving this?