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,
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
It worked!
I've changed it a little bit, just to avoid using filters but it definitely worked.
Thank you for your time.
:)