Analyze in Excel causes problems with dates

[MiKeZZa]

Hi all,

I'm facing some issues with date fields while Analyzing in Excel a Power BI report with an integrated model. I set the dateformat of a datetime field from SQL to this:

I would love to have a common European format: 14-03-2001 as dateformat, but this is not supported....

When I go to Excel with Analyze in Excel I get this:

So a you can see I lack al the possibilities that a datecolumn has in Excel Pivots. A normal pivot gives this:

This is caused by that it is not a datefield but a normal text. 

Can this problem be solved by something? Like choosing an other dateformat or something?

[quentin_vigne]

Hi @MiKeZZa

The european format is supported on PowerBI : 

As you see : 14/03/2001

[MiKeZZa]

Hi @quentin_vigne,

I thought I was completely insane when you posted your printscreen.... But I've looked again and can't find that options in my Power BI Desktop (most recent edition (may 2018) installed). See:

My regional settings are: Dutch (Netherlands), but I've also tried other European countries, like French (I assume you're using that when I see you're printscreen). But no effect....

[quentin_vigne]

@MiKeZZa

That's really strange !

I'm using French version from april 2018, but even when I had the may version it was fine.

I don't know what's the matter, perhaps english version only have the mm/dd/yyyy version and not the dd/mm

@v-yuezhe-msft any info on that ? 

[MiKeZZa]

Installing a Dutch version of Power BI Desktop did give me the options like you see! So in my visual it's better now; I have these dates:

01-01-11
04-01-11
05-01-11
06-01-11
07-01-11
10-01-11
11-01-11
14-01-11
18-01-11
19-01-11
24-01-11
01-02-11

So that's what I was looking for! Thank you for that.

But Analyze in Excel still behaves like it's a string in stead of a date.

Anyone some ideas about that?

[MiKeZZa]

I have a problem with the use of dates in analyzing in Excel. When I analyze a datamodel that is made in Power BI I don't receive the options like you get normally when you analyze a regular Pivot, like these:

But I get in Excel these options (the regular 'text' options):

Has anyone the solution for this???