How Do You Convert a Number to a Date in Power BI?

How Do You Convert a Number to a Date in Power BI?

A column arrives as 43910 where you expected a date, and every date filter, every time intelligence measure and every axis you build on it behaves strangely or refuses to work at all.

The fix is short, but almost every answer you will find online applies it to the wrong kind of number. There are two completely different things that show up as "a number that should be a date" in Power BI, they need different treatments, and Microsoft's own documentation covers one of them properly and names the other only to walk away from it.

That gap is worth understanding before you paste a formula, because the formula that fixes one case silently produces garbage on the other.

How do you convert a number to a date in Power BI? The direct answer

Do it in Power Query, not in DAX, and the function is Date.From.

Microsoft's documentation for Date.From is explicit about what it does with a number. It returns "the date component of the datetime equivalent of a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899."

So Date.From(43910) returns #date(2020, 3, 20). That is Microsoft's own worked example, not an inference.

If instead your number reads like a date spelled out as digits, such as 20260310 for 10 March 2026, Date.From is the wrong tool and will return a date thousands of years away. That case needs the digits split apart and reassembled, and it is handled further down.

Not sure whether your reports are built on clean date columns in the first place? Get a free SEO and analytics audit and find out what your data is actually telling you.

Why there are two kinds of "number that should be a date"

A day-count serial

This is a running count of days from a fixed starting point. 43910 is not a date in any readable sense; it is a tally. Spreadsheets have stored dates this way for decades, which is why this is what you get when someone exports a worksheet and the date formatting is lost on the way out.

Numbers in this family are typically five digits for anything in recent decades, and they increase by exactly one per day. If two rows a week apart differ by 7, you have a day-count serial.

A date written as digits

20260310 is the opposite idea. Nothing is being counted. The date is simply spelled out, year first, then month, then day, and then stored in a numeric column because whatever produced it had no date type available.

These are usually eight digits. Two rows a week apart differ by 7 only when they fall in the same month, and by an arbitrary jump when they cross a month boundary. That irregularity is the giveaway.

Why telling them apart matters more than the formula

Run a day-count conversion on 20260310 and Power Query will cheerfully count twenty million days forward from its base date and hand you a year in the distant future.

Nothing errors. The column turns into dates, the visual renders, and the numbers are nonsense.

How to convert a day-count serial, step by step

1. In Power BI Desktop, open Power Query by selecting Transform data on the Home ribbon.
2. Select the query holding the numeric column.
3. Confirm the diagnosis. Sort the column and check that consecutive days differ by 1. Five-digit values that step evenly are day-count serials.
4. Add a new column rather than overwriting the original, at least on the first pass. Use Add Column, then Custom Column.
5. Give it a name and the formula Date.From([YourNumberColumn]).
6. Set the new column's data type to Date using the type icon in its header.
7. Check a handful of rows against something you know independently, such as the first and last day in the source export.
8. Once the new column is right, remove the original numeric column and rename the new one to match what your measures expect.

How to convert a date written as digits

For 20260310, the shape of the answer is to pull out the year, month and day and rebuild a real date from the three pieces.

Microsoft's own DAX documentation raises this exact case and then declines to show you how. The DATE function page says the underlying data "might contain dates in a format that is not recognized as a date, such as YYYYMMDD" and that you "can use the DATE function in conjunction with other functions to convert the dates to a number that can be recognized as a date." Which other functions, and in what arrangement, is not stated anywhere on that page.

The reliable route is still Power Query. Convert the number to text, take the first four characters as the year, the next two as the month and the last two as the day, then assemble them with #date. Build it as a custom column, check it against known rows, and set the result's type to Date.

Reports are only as trustworthy as the data under them. Request a free audit and see where yours is quietly going wrong.

Why Power Query rather than DAX

This is the part most answers get backwards, and it rests on a sentence in Microsoft's documentation that is easy to read past.

The DAX DATE function page says that "in contrast to Microsoft Excel, which stores dates as a serial number, DAX date functions always return a datetime data type."

Read that carefully. DAX does not treat dates as serial numbers. There is therefore no documented DAX function that takes a day-count serial and interprets it, because the premise behind day-count serials does not exist in the DAX model. The base date of 30 December 1899 appears in Microsoft's documentation for Power Query's Date.From, and not on the DAX pages at all.

That is the technical reason. There are two practical ones as well.

A conversion done in Power Query happens once, during refresh, and every measure downstream sees a proper date column. A conversion done in DAX is a calculated column or a measure that has to be maintained, referenced correctly everywhere, and recomputed.

More importantly, a real date column in the model is what lets you mark a date table and use time intelligence. A number dressed up as a date in a measure does not give you that.

The DATEVALUE trap

If you search for this problem you will be pointed at DATEVALUE constantly. It sounds exactly right. It is documented for something else.

Microsoft's page for it describes it as converting "a date in text format to a date in datetime format." Its syntax is DATEVALUE(date_text), and the parameter is documented as "Text that represents a date."

Text. Not a number. The documentation does not mention numeric input at all, and it does not say what happens if you pass one. An undocumented behaviour is not a feature you want load-bearing in a refresh that runs every morning.

If you are starting from a number, DATEVALUE is not the function.

Limitations and mistakes worth knowing

The off-by-one worry. You will find many warnings that spreadsheet serial numbers and Power Query's base date disagree by a day because of a historical quirk in how one of them handles the year 1900. Microsoft's Date.From, DATE and DATEVALUE pages say nothing about this at all. They do not mention a leap-year discrepancy, and they do not say which serial numbers it would affect.

Rather than trusting a rule of thumb from a forum, convert your column and check two or three dates you can verify independently. If they are right, you are fine. If they are uniformly one day out, you know what to adjust and by how much.

Changing the type directly in the UI. Selecting a whole-number column and setting its type to Date often appears to work. The pages that document Date.From do not document the type-change UI as the same operation, so do not assume the two are equivalent. Use the explicit function, or at minimum check a known row after a type change.

The DATE function rolls values over instead of erroring. This catches people reassembling YYYYMMDD values. Microsoft documents that if you pass a month above 12, "the date is calculated by adding the value of month to the year", and gives DATE(2008,14,2) returning February 2, 2009. The same applies to days: pass a day beyond the end of the month and it is added on, so DATE(2008,1,35) returns February 4, 2008.

A parsing error in your text extraction will not raise an error. It will produce a plausible, wrong date.

Negative numbers. The DAX DATE documentation states that "negative integers are not supported", and for the year argument, values "greater than 9999 or less than zero" return a #VALUE! error.

A negative value in a column you believed was a date is a data quality problem, not a conversion problem.

Clean inputs are the cheapest performance win there is. Book a free audit and start with what is already broken.

How this compares to the alternatives

Fixing it at the source. If you control the export, make it emit real dates in a text format like YYYY-MM-DD and the problem disappears. This is always the best answer when it is available, and it is available more often than people assume.

Fixing it in Excel before import. Tempting, and fine for a one-off analysis. It fails for anything scheduled, because the fix lives in a file somebody has to remember to touch. Every refresh becomes a manual step.

A calculated column in DAX. Works, and is the right choice in the narrow case where the source is genuinely read-only, you cannot add a Power Query step, and you need the date only for display. Otherwise it costs you time intelligence and gains you nothing.

Doing it in Power Query. The default. One step, applied on refresh, producing a real date column that behaves like a date everywhere downstream.

The ranking is not close. Source, then Power Query, then DAX, and Excel only when nothing else is on the table.

How do you know the conversion worked?

Do not trust the preview. A column of dates that renders without error is the failure mode, not the success signal.

Three checks settle it. Compare the earliest and latest converted dates against the range you know the export covers. Pick two rows a known number of days apart and confirm the gap survived.

Then build one trivial measure using time intelligence against the column, because that is the thing a fake date column cannot do, and it will fail loudly if the type is wrong.

Final Thoughts

The whole problem reduces to one question asked before any formula: is this number counting days, or is it a date spelled out in digits?

For a day count, Date.From in Power Query, with the base date of 30 December 1899 that Microsoft documents. For digits, split and reassemble, and check your work carefully because DATE will roll a parsing mistake into a believable wrong answer rather than complaining.

Skip DATEVALUE entirely when you are starting from a number, and do the work in Power Query rather than DAX so that the model gets a genuine date column instead of a calculation that resembles one.

Once the column is right, the date-based work you actually wanted becomes straightforward. Our guide on how to calculate age in Power BI DAX picks up from there, and it is a good illustration of why having a real date column matters.

Want to know what else is quietly costing you in your reporting and your search traffic? Get your free audit.