Forum Discussion
Milestones Forecast vs Actual
Hi!
I have a coding background but I'm fairly new to M and DAX.
I received an excel file organized similar to this one (fake dataset):
| Index | M1 Forecast | M1 Actual | M2 Forecast | M2 Actual | M1 Success | M2 Success |
| 1 | 01/01/2010 | 05/01/2010 | 03/02/2010 | 03/02/2010 | FALSE | TRUE |
| 2 | 17/01/2010 | 17/01/2010 | 09/02/2010 | 10/02/2010 | TRUE | FALSE |
| 3 | 22/01/2010 | 23/01/2010 | 15/02/2010 | 15/02/2010 | FALSE | TRUE |
| 4 | 25/01/2010 | 26/01/2010 | 26/02/2010 | 28/02/2010 | FALSE | FALSE |
Where M is for Milestone (and of course the columns and the rows are really in the hundreds).
I basically compare the "Forecast" and the "Actual" dates to evaluate if the Milestone was completed on time.
I would like to:
1. Have a list of Milestones that I can use as a filter.
2. Automate the creation of the "Success" column, to avoid the copy/paste/edit repetitive task for each milestone.
My idea (pseudocode badly mixing Java and M):
- create an array of array: arr = {{M1F,M1A}, {M2F,M2A}}
- loop through it: for(i = 0; i<arr.lenght; i++) { Table.AddColumn(MyTable, Milestones[i], each if arr[i][1]< arr[i][0] then true else false )}
Where Milestones[i] is the String value name of the Milestone, from the Milestones list.
Any idea how to do this in Power Query or DAX?
Thanks
Hi Anonymous
Please see the attached file with a solution.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
2 Replies
- Mariusz
Community Champion
Hi Anonymous
Please see the attached file with a solution.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.- AnonymousNot applicable
That's great, Thanks Mariusz!