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
