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 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).
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