Forum Discussion
Time intelligence does not work
Got a solution from PBI support.
========SOLUTION TEXT STARTS========
One easy way to solve the ussie is to transform the date from a text to a date value using the UK format:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVRRcsUgCLxLvjsjYIzmLJl3/2tUGht3ie2fDALLsnBdW9YkOZnosX2+rq2/b7P+mFmSnNPbTbVp6p5k589o9sxtmnYO7x1rNamAaUkVzJbEZmx/S4FUQiDde4C3cCGODf32ovjZG0QYQrFaBhuj7jG8a3IOQiV7LHR7x+dzcPWQI7rAPFI1guFeZXJWsZMNgPEQO2BkBqmMqr3mC6i8BeBKAlfGQgqx/Fm5bpcZSkUbNRhEqCwzhwGpfAoVUhWObaRYBwnkOO2V2QCJ2q939FtfTJZF3SkzYxh10dEwhToKG/rmmTR5siYZZCQnaLKwzCphDgNdaANbMDKDroRX0jVZmEnWBtGe+W4odRRQ9bq47GH3+5vMzCDfV+XvI+MwYNnDJQw89/HhBMOdjKdAaXF8KCezAXXjuHk1/F7xqaeRFa57EDmO2f5lA2b0HNUe+/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
in
#"Changed Type with Locale"
In the Query Editor > Advanced query editor, you have to replace your
#"Changed Type with Locale"= ……………………………….” (whatever is there in middle), please replace this with the below one and close and apply and now run the DAX query will work
#”Changed Type with Locale” = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")========SOLUTION TEXT ENDS========
For anyone still encountering this issue, this fix worked for me. In retrospect this is probably quite obvious to the experienced users here, but for those of us still learning it isn't quite so clear.
Assuming you have created a custom date dimension, right click on the table name in your fields list. Navigate down to "Mark as date table" and make sure it is checked. Now I no longer recieve the error at the top of the thread.
I did this but message " The Date Column Can't have gaps in dates" and I am sure there are no gaps