topic Calculated Column Filter in DAX Commands and Tips

Calculated Column Filter

Anonymous — Sun, 21 Jun 2020 09:09:07 GMT

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.

Area	Country	Business Unit	Month / Year	Income
			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 / Year	Income
June 2020	Income (Total) - Income (Business 1) - Income (Business 2)
July 2020	Income (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.

Re: Calculated Column Filter

lbendlin — Sun, 21 Jun 2020 17:14:21 GMT

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)

Re: Calculated Column Filter

Anonymous — Thu, 09 Jul 2020 07:18:01 GMT

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.