Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Column Filter

Hi all,

 

I am hoping to get some help from other PBI users on the below. I have three sets of hisorical data which are refreshed on a periodical basis. The data sits in three different tables:

1. TOTAL

2. BUSINESS 1

3. BUSINESS 2

 

The three tables have the same amount of column as below.

AreaCountryBusiness UnitMonth / YearIncome
   April 2020 
   May 2020 

 

The things that I need to do is to perform a future income projection based on the following formula (Assuming I now have historical data of the past 2 months, to find the income for month 3 and onward, I will also assume that it is a constant value):

Month 3 and onward income = Income (Total) - Income (Business 1) - Income (Business 2)

 

The requirement is such that I will output the data in 12 consecutive months:

Month / YearIncome
June 2020Income (Total) - Income (Business 1) - Income (Business 2)
July 2020Income (Total) - Income (Business 1) - Income (Business 2)
... 

 

However when I apply slicer filter, the value does not change (If I were to use MEASURE, the value will change accordingly, however this does not work for COLUMN). I am using COLUMN as I would like to display the values in consecutive 12 months.

 

I have tried to apply:

- filter = selectedvalues, this does not work

- relatedtable, this does not work too

 

Anyone with similar experience who might be able to give me some pointers, please? Thank you.

Calculated columns

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    How is this approach supposed to help you with income projection? 

    You can plot your existing data and then in the analytics section choose a forecast line.

    Alternatively, pick an extrapolation formula and apply it.  Predicting one month from the performance of another mont will be rather crude - you will want to consider seasonality too (ie you want to include the same month last year in your formula)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your reply. The projection is a very crude one and makes assumption that in the following months, the expected income is equal to the average of the past data. Therefore the constant value - yes it is indeed not a very accurate one due to the big uncertainties in the business. 

       

      Anyway I have managed to resolve this by creating a new table to put all data into a single table. Thank you.