calculated column does not work in published report - only in desktop app

[Ines_SAG]

Hi,

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?

[Ines_SAG]

Thank you, making sure that the fields are the same format (whole number in this case) has solved the issue. 

Many thanks @v-tangjie-msft 

the new formula for the calculated column (in case anyone is interested) looks like this
("is weekday" has replaced TRUE with 1)

Duration Days GDD = if(SearchResults[SubmittedHelper]=0,0, if(SearchResults[HelperCompleted]=0,

CALCULATE(COUNTROWS ( 'Date'),DATESBETWEEN ( 'Date'[Date],SearchResults[Submitted Date]  , SearchResults[Today]  ),

    'Date'[isweekday] = 1, ALL ( SearchResults )),

 CALCULATE(COUNTROWS ( 'Date'),DATESBETWEEN ( 'Date'[Date],SearchResults[Submitted Date]  , SearchResults[Completed Date]  ),

    'Date'[isweekday] = 1, ALL ( SearchResults ))))

[v-tangjie-msft · Accepted solution]

Hi  @Ines_SAG ,

Please try updating the data and republishing PBIX on Desktop.

Please check whether the added data is in the same format as the data source, from the calculated column formula we can find that column [SubmittedHelper] , column [HelperCompleted] and column [[isweekday]] are in text format.

Best Regards,

Neeko Tang

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.