Excel DAX expressions with errors in Power BI

[bowater]

I am VERY VERY NEW to Power BI. I am working on a churn analysis and have built a dashboard using excel/powerpivot/powerquery that funtions very nicely. I would like to be able to share this easily without connections updated every time by recipient and thought I would try and build it in Power BI. All the expressions are written in DAX expressions and function perfectly in Excel.

I have imported data (Excel file) and built out a date table as well as two other query tables. The relationships are linked properly. I have also added all the measures identical to what I built in PowerPivot. Some of the measures are calculations based on date but the visuals throw out this error:
MdxScript(Model) (1, 88) Calculation error in measure 'FACT TABLE'[Cancelled]: An invalid numeric representation of a date value was encountered.

Here is the formula I am using that works correctly in Excel:

Cancelled = CALCULATE(FACT TABLE[TotalToDate],DATESBETWEEN(FACT TABLE[Churn Date], FIRSTDATE('Dates'[Date]),LASTDATE('Dates'[Date])))

TotalToDate = CALCULATE(DISTINCTCOUNT(FACT TABLE[id]),FILTER(ALL('Dates'[Date]),'Dates'[Date]<=MAX('Dates'[Date])))

What am I missing here?

[KHorseman]

There's probably nothing wrong with your DAX, but rather with your data. I usually encounter this error message when I have a date in my fact table that is not in my date table. My standard date table runs from 1/1/2014 -12/31/2021, and one time some joker entered a contract signed date of 7/15/2099 in our database. It took me two hours to figure out what was preventing the dataset from refreshing the following day.

Anyway, start by checking the min and max values in that churn date column and see if they're out of range. If that doesn't work you'll have to try looking for invalid dates, like September 31.

[bowater]

I have cehcked the dates and they work. I used Power Query to make a Dynamic date table and with Power BI, i just took it out to 2025....could that be it?

[KHorseman]

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.

[bowater]

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??

[KHorseman]

That shouldn't matter either. Unless they're not true blanks, like if your query enters 0 instead of leaving null values.

[bowater · Accepted solution]

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"

[KHorseman]

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?

[bowater]

To be honest, I could not find it. The first table was not dynamic but once I made it dyanmic it worked perfectly. I looked for zeros, nulls, odd dates and couldn't find anything.

I think I am just going to take this as a win and run with it:)