I would like to be able to Count a value of a Field if I have a Starting Number and a Start Date

[trevor50K]

I need to be able to count the number of fields with a TRUE value from a Start Date.

I have two tables

'WORKABLEDATES'

[DATE]            [WORKABLE]

1/1/2019         TRUE

1/2/2019         TRUE

1/3/2019         FALSE

1/4/2019         TRUE

'PROJECT'               

[STARTDATE]                     [LOE]

1/1/2019                            3

I want to make a Colmun called [ENDDATE]

I know this is not anywhere near a correct Syntax, but using the wording to help describe what I need.

[ENDDATE] = IF('PROJECT'[STARTDATE]='WORKABLEDATES'[DATE] COUNT [WORKABLE] when TRUE until COUNT=[LOE] RETURN [DATE]

Sorry I had trouble even articulating the question but i need to be able to count the number of times the WORKABLE FIELD is TRUE and when it reaches the LOE Number return the DATE from the WORKABLE DATES Table.

[jdbuchanan71 · Accepted solution]

Let me see if I understand.  I will assume that "workable" is the same as weekday just for the example.

This means, if a project starts on a Friday and has an LOE of 4 you want the date of the 4th working day after the start date.  In other words the project will be worked on Friday, Monday, Tuesday, Wednesday and you want the date of the Wednesday, yes?

This code woked for me as a calculated column.

End Date = 
VAR _ProjDate = PROJECT[Start Date]
VAR _LOE = PROJECT[LOE]
RETURN
    MAXX (
        CALCULATETABLE (
            TOPN (
                _LOE,
                CALCULATETABLE (
                    VALUES ( WORKABLEDATES[Date] ),
                    FILTER (
                        WORKABLEDATES,
                        WORKABLEDATES[Workable] = TRUE
                            && WORKABLEDATES[Date] >= _ProjDate
                    )
                ),
                WORKABLEDATES[Date], ASC
            )
        ),
        WORKABLEDATES[Date]
    )

I have attached my sample file for you to look at.