The Staff Turnover Calculation video is really great and I will definitely be using that.
I think I should have explained the scenario a bit differently. The error in Power BI occurs immediately after changing a data type and loading the data. Here’s the error message:
DataFormat.Error: We couldn’t parse the input provided as a Date value.
I actually found the problem and it was the fact that the data was in UK format not USA format, and the reason Power BI did not pick that up was because the dates in the regular Date column all looked liked USA format since the day was always the 1st of each month for all the rows.
But, when I changed the data type of the Termination Date column (a different column) in the Query Editor, Power BI raised the date “14/12/2012” as an error, not realizing that it was in UK date format.
I did not pick this up right away, but when I saw my data not behaving as expected while creating a report, I started digging to find the problem.
The Solution is to change the Regional Settings / Locale for import to English (United Kingdom).