Advanced Filtering (Real vs. Estimation)

[as3znaus]

Hi!

I want to create a table to organize my company's costs, but the "data base" structure is driving me crazy..

As an example, I have the following:

PROJECT	STAFF COSTS	DIRECT COSTS	DATE	REPORT
1143	56	78	01/01/2017	1
1143	56	45	01/02/2017	1
1143	45	85	01/03/2017	1
1143	52	54	01/04/2017	1
1143	78	54	01/05/2017	1
1143	45	12	01/06/2017	1
1143	65	32	01/07/2017	1
1143	65	84	01/08/2017	1
1143	54	54	01/09/2017	1
1143	68	56	01/10/2017	1
1143	89	54	01/11/2017	1
1143	78	85	01/12/2017	1
1143	56	78	01/01/2017	2
1143	78	54	01/02/2017	2
1143	45	85	01/03/2017	2
1143	52	54	01/04/2017	2
1143	78	54	01/05/2017	2
1143	45	12	01/06/2017	2
1143	65	32	01/07/2017	2
1143	65	84	01/08/2017	2
1143	54	54	01/09/2017	2
1143	68	56	01/10/2017	2
1143	89	54	01/11/2017	2
1143	78	85	01/12/2017	2

As you can see, I have monthly reports with costs of the entire year, the bold numbers are real costs and the rest are estimations.

I want to create a table or a chart that represent the diference between estimations and real costs of the project, in order to measure the differences between theses values.

I have try to add new columns, filtering by both "DATE" and "REPORT" but I get a lot of columns in my queries and it's hard to work with this files.

Hope someone can give me a hand!

Thanks

[tringuyenminh92]

Hi @as3znaus,

As my understand, the Real data are rows that has Day of date <= Report value of that row ? So that, please check my sample:

Create new Calculated column as:

Act/Est = IF( Planning[DATE].[Day] <= Planning[REPORT] ,"Actual","Estimate")

In case you need any further analysis, please let me know.

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[as3znaus]

Hi @tringuyenminh92,

I really appreciate your quick respond.

It works well, but I would like to maintain the estimations and put the real cost side-by-side.

¿any idea?...I guess I should consider the first month of the project as an estimation and freeze these values. And then use your Act/Est to compare.

Thanks!

[tringuyenminh92]

Hi @as3znaus,

it's good to hear that. What should i understand the meaning of "put the real cost side-by-side" and "consider the first month of the project as an estimation".

If you have any further requirements, just show me your expectation in pictures and I could propose possible approaches.

[as3znaus]

Ok, let me explain myself better 🙂

Imagine that these values are the cost estimations for the project 1143.

PROJECT	STAFF COSTS	DIRECT COSTS	DATE	REPORT
1143	56	78	01/01/2017	1
1143	56	45	01/02/2017	1
1143	45	85	01/03/2017	1
1143	52	54	01/04/2017	1
1143	78	54	01/05/2017	1
1143	45	12	01/06/2017	1
1143	65	32	01/07/2017	1
1143	65	84	01/08/2017	1
1143	54	54	01/09/2017	1
1143	68	56	01/10/2017	1
1143	89	54	01/11/2017	1
1143	78	85	01/12/2017	1

So, the project started on January (please notice that my dates are DD/MM/YYYY) and I have an estimation for each month of the year. Now, I want yo create a chart with all the estimations and freeze it, something like this:

Ok so imagine now we are in March and I have reports 1,2 and 3 ready (one for each month). I want the above chart to have a bar "in a different color" on January, February and March representing the real cost that I get from the report.

It's kind of complicate, so if you need further details I can create an example file.

Thanks for your time!

[as3znaus]

As you can see in these charts every month I replace the estimations with actual data, so I need to "freeze" my initial estimations to be able to compare the values (actual vs. estimations).

[tringuyenminh92 · Accepted solution]

Hi @as3znaus,

Sorry for late response, i just finished dinner after work ^_^ I think i got your point now, it's planning and actual timeline. 

So i will create Act/Est column with a little changes: 

Act/Est = if(YEAR(TODAY()) <> Data[DATE].[Year],BLANK(),if(Data[REPORT]=0,"Estimado",if(MONTH(TODAY())=Data[REPORT] && Data[DATE].[MonthNo]<=Data[REPORT], "Real",if(MONTH(TODAY())=Data[REPORT],"Estimado",BLANK()) )) )

• There is condition with year, cause i think you will use this report for years in future.
• Report = 0 ( 12 rows estimation of this project)
• There is comparison with month(today()), cause i understand that this statistic will be changed every months
• Actual & Estimate: 
  1. Actual: rows that belongs this year and [Report] = current month and MonthNo <= [Report] (to get actual rows of this reporting time)
  2. Estimate: the rest of rows that belongs to this reporting time [Report] 
• Another rows will be blank for filter purpose (i used filters in Visual level filter as picture)

 Sample: https://www.dropbox.com/s/9qzj8zhaov4ymro/Act_est.pbix?dl=0

data: https://www.dropbox.com/s/sqxzr6a76oz2j0u/Planning.xlsx?dl=0

[as3znaus]

@tringuyenminh92 

It worked!

I've changed it a little bit, just to avoid using filters but it definitely worked. 

Thank you for your time.

🙂