Forum Discussion
Advanced Filtering (Real vs. Estimation)
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
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
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!
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.
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!
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).