I have built a model containing a list of projects and the different stages of these. we use the data from the list to determine resourcing levels. have been doing it in excel previously. its like a big gant chart. in excel we have week ending dates running across the top as column headers. to display progress we compare the start date plus the completion status against these column headers. Very simply, if start date (in the body of the data table) is greater than the first column header (its in date format) and end date is less than the second column header (also in date format), show complete under those dates.
Columns AC and AE contain the start date and end date for each project, column AI is the completion %, BA2, AH2 and AH3 contain characters that represent different stages of the project. not important for this question.
I have created columns in power bi that replicate the Date column headers. they are text headers. how do i write a function that will do the comparison above? that is, how do i compare the start date with the column headers?
| Procurement Forward Plan (Live) Jan 24 (copy).xlsb | ||||||
|---|---|---|---|---|---|---|
| AS | AT | AU | AV | |||
| 44 | 01 Apr 23 | 08 Apr 23 | 15 Apr 23 | 22 Apr 23 | ||
| 45 | n | n | n | |||
| 46 | ||||||
| 47 | ||||||
Forward Plan | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AT44:AV44 | AT44 | =AS44+7 |
| AS45:AV47 | AS45 | =IF(ISERROR($AC45),"",IF(AND($AE45>AR$44,$AE45<=AS$44,$AI45=100),$BA$2,IF(AND($AE45>AR$44,$AE45<=AS$44),$AH$2,IF($AE45<AS$44,"",IF(AND(AS$44>=$AK45,AS$44<=$AE45),$AH$3,IF(AND(AS$44>=$AC45,AS$44<=$AK45),$BA$2,"")))))) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| AS44:EN44 | Cell Value | <$AG$2 | text | NO |
| BA2,EP45:EP2437,AS45:EN2437 | Cell Value | ="b" | text | NO |
| BA2,EP45:EP2437,AS45:EN2437 | Cell Value | =$BA$3 | text | NO |
| BA2,EP45:EP2437,AS45:EN2437 | Cell Value | =$AZ$2 | text | NO |
| BA2,EP45:EP2437,AS45:EN2437 | Cell Value | =$AH$3 | text | NO |
| BA2,EP45:EP2437,AS45:EN2437 | Cell Value | =$AH$2 | text | NO |
Columns AC and AE contain the start date and end date for each project, column AI is the completion %, BA2, AH2 and AH3 contain characters that represent different stages of the project. not important for this question.
I have created columns in power bi that replicate the Date column headers. they are text headers. how do i write a function that will do the comparison above? that is, how do i compare the start date with the column headers?
