Forum Discussion
Advanced Filtering (Real vs. Estimation)
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
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:
- Actual: rows that belongs this year and [Report] = current month and MonthNo <= [Report] (to get actual rows of this reporting time)
- 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
7 Replies
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 NguyenHi 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!
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.
