Hi team,
I have the attached excel file with test data that I would like to eventually import into power bi or power query using the following columns:
Column A (Initial Test Date) will never be blank
Column B (Passed Y/N?) will always say "No"
Column C (Recent Date) will never be blank
Column D (Status) will either be "Re-testing" or "Passed"
If
I am trying to count the number of months between the two dates (Initial Test Date & Recent Date), and for each of the months
between those two dates to enter a count of 1 only if the following conditions apply:
1. For example on row 2, if Status = Re-testing, then I want to enter a 1 for the month of April and 1 for the month of May (current month) because we
have to count the month from the "Initial Test Date" column and since it did not pass, we have to also include a 1 for the current month; see results K2:L10
2. On row 11, the status is "Re-testing", so we need to enter a 1 for each of the months from Jan thru May
3. same rule applies as row 11, except we now have to also enter a 1 for the month of Dec of the previous year
4. On row 15, since the status is "Passed", the Initial Test Date is in Apr, and it passed testing also in Apr, then we only need to enter a 0 in the month of Apr
5. On row 16, since the status is "Passed", the Initial Test Date is in Mar, and it passed testing in the month of Apr, then we need to enter a 1 for the month of Mar and 0 for the month of Apr
If the status is "Passed", then we do not need to count the month in which "Recent Date" falls on
Also, Column A (Initial Test Date) and Column C (Recent Date) could go back as far as Jan 2006, so I would like to take that into consideration, but only provided a short dataset for simplicity![84087 84087](https://chandoo.org/forum/data/attachments/84/84179-a5dfbd490c384559dfa524c0b025793e.jpg)
Thank you so much for your help in advance! Hope this is feasible. If it cannot be done in either power query or power bi, what would be the solution in excel
I have the attached excel file with test data that I would like to eventually import into power bi or power query using the following columns:
Column A (Initial Test Date) will never be blank
Column B (Passed Y/N?) will always say "No"
Column C (Recent Date) will never be blank
Column D (Status) will either be "Re-testing" or "Passed"
If
I am trying to count the number of months between the two dates (Initial Test Date & Recent Date), and for each of the months
between those two dates to enter a count of 1 only if the following conditions apply:
1. For example on row 2, if Status = Re-testing, then I want to enter a 1 for the month of April and 1 for the month of May (current month) because we
have to count the month from the "Initial Test Date" column and since it did not pass, we have to also include a 1 for the current month; see results K2:L10
2. On row 11, the status is "Re-testing", so we need to enter a 1 for each of the months from Jan thru May
3. same rule applies as row 11, except we now have to also enter a 1 for the month of Dec of the previous year
4. On row 15, since the status is "Passed", the Initial Test Date is in Apr, and it passed testing also in Apr, then we only need to enter a 0 in the month of Apr
5. On row 16, since the status is "Passed", the Initial Test Date is in Mar, and it passed testing in the month of Apr, then we need to enter a 1 for the month of Mar and 0 for the month of Apr
If the status is "Passed", then we do not need to count the month in which "Recent Date" falls on
Also, Column A (Initial Test Date) and Column C (Recent Date) could go back as far as Jan 2006, so I would like to take that into consideration, but only provided a short dataset for simplicity
![84087 84087](https://chandoo.org/forum/data/attachments/84/84179-a5dfbd490c384559dfa524c0b025793e.jpg)
Thank you so much for your help in advance! Hope this is feasible. If it cannot be done in either power query or power bi, what would be the solution in excel