I Have a column named new year which extracts fiscal year from a given date column. The problem is that this new year column have whole number data type and when I converted it into date data type then the dates inside the new year column changed.
Before converting data type into date format
After converting data type into date format
I was expecting new year column to change data type to date format without any change in year but as you can see whole year changed. Can anyone please explain me why this is happening and how to fix it?
1
the reason this is occurring is because dates are not stored as intuitive segments in the Power BI backend. Instead, the date parts are concatenated together in a string-like format and stored as an integer. It is only parsed and presented to you as a date. Because of this, some datatype conversions can disrupt this parse and cause the issue you are seeing.
In fact, the error you are seeing is not (solely) an issue due to the DATEADD or IF clauses – I was able to replicate the problem just using the YEAR clause.
To resolve this, instead set the column datatype to be Text instead of Whole Number. If you definitely need a Whole Number as a result, create a second column and cast the intermediate Text column into a Whole Number using that.
It’s the same in Excel. When you transfer 2024 to date in Excel, you will get almost the same date.
if you want a date type , you can try this
new year = date(if(month(date)<4,year(date)-1,year(date)),1,1)