Hi everyone,
I have been working on Power BI for a few months and now reached a point where I really need help. So I hope you can and I already thank you for it.
Here is the situation :
My hierarchy is as follow :
Raw budget
| Project | Phase | Budget |
| A | 1 | x |
| A | 2 | x |
| A | 3 | x |
| A | 4 | x |
Detailed budget
| Project | Phase | Product | Task | Time Budget |
| A | 1 | a | uniqueID | x |
| A | 1 | a | uniqueID | x |
| A | 1 | b | uniqueID | x |
| A | 1 | b | uniqueID | x |
| A | 2 | a | uniqueID | x |
| A | 2 | a | uniqueID | x |
| B | 1 | v | uniqueID | x |
| B | 1 | v | uniqueID | x |
Time tally table
| Project | Phase | Product | Task | Time Tally | Date |
| A | 1 | a | uniqueID | x | ddmmyyy |
| A | 1 | a | uniqueID | x | ddmmyyy |
| A | 1 | a | uniqueID | x | ddmmyyy |
| A | 1 | b | uniqueID | x | ddmmyyy |
First I want to calculate the remaining time
I tried this :
Measure = VAR Timeleft = SUM ( DetailedBudget [Time] ) - SUM ( Timetally [time] )
RETURN
IF ( Timeleft > 0 ; Timeleft )
And its working when shown again the Task dimension but its wrong when I show it for a project (The total time spent is greater than the budget so this measure will show 0 time left when used with project dimension.
I kind of made it work with calculated columns but then the time left to do is not updating depending on the date slicer.
Then, I need to check for a given Phase, if there is no Detailed time budget then use the Raw Budget.
the result would be something like that:
| Projet | Phase | Raw Budget | Detailed Budget | Used Budget | Time spent | Time left |
| A | 1 | 10 | 15 | 15 | 1 | 14 |
| A | 2 | 20 | 15 | 15 | 2 | 13 |
| A | 3 | 30 | | 30 | 3 | 27 |
| A | 4 | 40 | 35 | 35 | 4 | 31 |
Thank you very much for reading through this and please ask me if you need extra details. I'm so overwhelmed by this that just explaining it is difficult.
Cheers!
