Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, therefo...
Need Help
  • AlB's avatar
    AlB
    7 years ago

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

     

ripstaur's avatar
ripstaur
Helper III
5 years ago

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/time_series_covid19_deaths_US.csv (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's avatar
Batman501
New Member
4 years ago

Hey, any luck on solving this?