Reply
Highlighted
Member
Posts: 116
Registered: ‎01-12-2017

Analyze in Excel causes problems with dates

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:

 

analyze_2.png

 

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:

analyze_1.png

 

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

filterdatebetween01[1].png

 

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?

Senior Member
Posts: 391
Registered: ‎04-24-2018

Re: Analyze in Excel causes problems with dates

Hi @MiKeZZa

 

The european format is supported on PowerBI : 

 

changedate.png

 

As you see : 14/03/2001

Member
Posts: 116
Registered: ‎01-12-2017

Re: Analyze in Excel causes problems with dates

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:


analyze_3.png

 

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

Senior Member
Posts: 391
Registered: ‎04-24-2018

Re: Analyze in Excel causes problems with dates

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

Member
Posts: 116
Registered: ‎01-12-2017

Re: Analyze in Excel causes problems with dates

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?

Member
Posts: 116
Registered: ‎01-12-2017

Use dates in analyzing in Excel

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:

 

filterdatebetween01[1].png

 

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

 

analyze_1.png

 

Has anyone the solution for this???