Forum Discussion
Excel DAX expressions with errors in Power BI
Thanks for the input!! It was the Date table that was causing the issue not the fact table. I deleted the table and recreated using advanced editor and a dynamic date table with the following and it worked like a charm:
let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type2", "Quarter", each "Q" & Number.ToText([Quarter Number],"0")),
#"Inserted Week of Year" = Table.AddColumn(#"Added Custom7", "WeekOfYear", each Date.WeekOfYear([Date]), type number)
in
#"Inserted Week of Year"
I don't think so. 2025 is a valid year after all. As I understand it, that error message specifically means "I'm trying to compare a date value from one table to the date column in your date table, and I can't find it there. I guess that means this isn't a date, so I'll try using it as a number. Wait, I can't do that because this is a date comparison. Abandon ship!" Y'know, if Power BI was a slightly befuddled guy and he talked to you like a person instead of software error messages.
It may not be that churn date column at all. If you have other date columns elsewhere that are also related to your date table, it could be any of them. Check every date column with a relationship to the date table. One of them almost certainly has a date that is either higher or lower than the range covered in the date table. If it's not that, it's an invalid date like February 30th or something, and those are harder to track down.
Some of the Churn Date fileds are blank because the subscription is still active. If that is the case, I need to put something in to ignore Blanks??
That shouldn't matter either. Unless they're not true blanks, like if your query enters 0 instead of leaving null values.
Thanks for the input!! It was the Date table that was causing the issue not the fact table. I deleted the table and recreated using advanced editor and a dynamic date table with the following and it worked like a charm:
let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type2", "Quarter", each "Q" & Number.ToText([Quarter Number],"0")),
#"Inserted Week of Year" = Table.AddColumn(#"Added Custom7", "WeekOfYear", each Date.WeekOfYear([Date]), type number)
in
#"Inserted Week of Year"Cool. I've never seen that error come from the date table itself. Was there an invalid date in your date table or something like that?