Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.