cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
featleyd
Helper V
Helper V

Dates

Hi all

 

Just a question around dates. 

 

I've completed a few reports where I've added a date table in to create a relationship with the data itself.

 

Once I add my Many to One or One to One relationahips in, I often lose date heirachy functionality on one side of the realtionship?

 

Is there any way I can do this a different way to ensure I retain heirachy functionality on both sides of my relationship?

 

In this example its a One to One relationship, as you can see I have retained Heirachy function the Collection Date, but lost it on the Date column.

 

featleyd_0-1600688194111.png

featleyd_1-1600688219563.png

 

Any advice?

 

Thanks!

2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@featleyd , When you create a date table and mark it date column it will ask for date that will become Key. So you will not have a hierarchy.

You can create a custom hierarchy or have another date column that can have hierarchy

Date 1 = [Date]

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

FrankAT
Super User II
Super User II

Hi @featleyd 

you have to keep two differences in mind:

 

  1. The first icon is used for a table that is set as calendar table. In the calendar table, you can create any own hierarchies. In model view, simply drag the month column over the year column, and so on.

  2. The second icon marks a column that contains dates and is internally managed by Power BI Desktop and provided with a date hierarchy. This operation can be influenced in the options settings. You have no influence on these hierarchies!

 

Take a look at tab File > Option and settings > Option > Load Data (Global) > Time intelligence > Auto date/time for new file.

Take a look at tab File > Option and settings > Option > Load Data (Current File) > Time intelligence > Auto date/time.

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

View solution in original post

12 REPLIES 12
Sanmelmer
Regular Visitor

Hi!

Im trying to have a graphic by week.

The dates are between  these weeks: 27 /2020 and 2/2021

The problem is that the graphic doesnt understand w1 and w2 of 2021 should go later w53 2020. And they appear before w27/2020.

When i put the dates by day instead of weeks, it works ok! but not with the weeks 😞

 

I created a table with the dates:

WEEKNUM('RANGO FECHAS'[Date],2)
 
Please could you help me??
 
thanks!!

Could you not add a new column to your date table, which specifies the unit of time that you are looking for?

Maybe by using a SWITCH statement you could say for example:

 

Date (UK format)    Desired format

1/1/2021                 W53

 

Not a perfect solution and I'm sure cleverer people than me will have more insight, but that could get the job done.

Hi ! and thanks for your reploy 

I already have these columns , the problem is with the graphic ==> 

 

semanas.png

InkedCaptura de pantalla _LI.jpg

My guess would be that the format you have isn't a recognised date format, and that Power BI is interpreting it as a number?

Hello!

no, it is a date and its recognized.  

The problem is that I want to make a graphic with the WEEKS in the X axis. 

I have weeks from 2020 and 2021. The weeks 1, 2... from 2021 should go after w53 of 2020. But Power bi doesnt recognize that should go like that and put w 1,2... before in a logical order. 

(If i put the days (not weeks) it is ok! but i need the weeks in the graphic) 

FrankAT
Super User II
Super User II

Hi @featleyd 

you have to keep two differences in mind:

 

  1. The first icon is used for a table that is set as calendar table. In the calendar table, you can create any own hierarchies. In model view, simply drag the month column over the year column, and so on.

  2. The second icon marks a column that contains dates and is internally managed by Power BI Desktop and provided with a date hierarchy. This operation can be influenced in the options settings. You have no influence on these hierarchies!

 

Take a look at tab File > Option and settings > Option > Load Data (Global) > Time intelligence > Auto date/time for new file.

Take a look at tab File > Option and settings > Option > Load Data (Current File) > Time intelligence > Auto date/time.

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

View solution in original post

Hi @FrankAT 

 

Thanks for your reply. I suspect I did not make myself clear in my original question.

 

I have resolved the heirachy issue, so thank you for your's and @amitchandak  assistance.

 

Kind Regards

 

 

@featleyd , In case non of the above solutions, worked. Post your solution and mark that as the solution. So others can get benefited. If something above worked, mark that as a solution. 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@featleyd , When you create a date table and mark it date column it will ask for date that will become Key. So you will not have a hierarchy.

You can create a custom hierarchy or have another date column that can have hierarchy

Date 1 = [Date]

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @amitchandak 

 

Thanks for your response.

 

I had tried adding a further date, but I have just tried again but it does not give me the heirachy?

 

Here is the column I added:

featleyd_0-1600689275393.png

Here is the resulting column type:

featleyd_1-1600689302645.png

And as you can see, no heirachy available:

featleyd_2-1600689328439.png

 

Am i missing something obvious?

 

Thanks

 

Any further views on this issue?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.