I have a report detailing (among other things) how long certain processes have taken from their start date to their end date in working days. There is a date table ("Date") in the data model that shows all dates and states whether it is a working day or not and a calculated column in the main data table ("SearchResults") uses that to give the duration in working days.
This works reliably in the desktop app and it also works immediately after publishing it to a workspace.
However, if I publish again to the workspace (e. g. after having changed some other details) or refresh the dataset on the workspace, this calculation stops working and will only return blank values.
This is what the calculated column does:
Duration Days = if(SearchResults[SubmittedHelper]="0",0, if(SearchResults[HelperCompleted]="0",
CALCULATE(COUNTROWS ( 'Date'),DATESBETWEEN ( 'Date'[Date],SearchResults[Submitted Date] , SearchResults[Today] ),
'Date'[isweekday] = "TRUE", ALL ( SearchResults )),
CALCULATE(COUNTROWS ( 'Date'),DATESBETWEEN ( 'Date'[Date],SearchResults[Submitted Date] , SearchResults[Completed Date] ),
'Date'[isweekday] = "TRUE", ALL ( SearchResults ))))
what I wanted it to do basically:
If an item has not been submitted = 0
If an item has been submitted but not completed = today - submitted date (in working days)
If an item has been submitted and completed = completed - submitted (in working days)
(not completed/submitted is shown by the helper columns that show 0 if the respective date column contains a blank)
Can anyone help me understand what the issue is and how to fix it?