Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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...
  • AlB's avatar
    6 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_
        )