Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MiKeZZa
Post Patron
Post Patron

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?

5 REPLIES 5
quentin_vigne
Solution Sage
Solution Sage

Hi @MiKeZZa

 

The european format is supported on PowerBI : 

 

changedate.png

 

As you see : 14/03/2001

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

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

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?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.