Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

IndexM1 ForecastM1 ActualM2 ForecastM2 ActualM1 SuccessM2 Success
101/01/201005/01/201003/02/201003/02/2010FALSETRUE
217/01/201017/01/201009/02/201010/02/2010TRUEFALSE
322/01/201023/01/201015/02/201015/02/2010FALSETRUE
425/01/201026/01/201026/02/201028/02/2010FALSEFALSE

 

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

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      That's great, Thanks Mariusz!