Months in wrong order

[K_Hermann]

Hello, I need help with a subject.

I have a few KPI's which are numbers, a target which is a number and the months.

If I make a cauge I can select the current month, july. But if I make a KPI visualisation or table/graph to compare the data of this current months with the months before I get the months in the wrong order. It compares june with july and august instead of august with july and june. In the graphs the numbers also start with june (0,2), not august (1,0). And in the table they start with the current month. I tried the ascending/descending in the database but I can't seem to get the months in the right order. How do I do this?

[Anonymous]

Do you have a Calendar table? Could you send a print of the field list (on the right)?

[K_Hermann]

[Anonymous]

OK, so you should add a Calendar table first, clik "Add Table" and write like:

Table Name = CALENDAR (FIRSTDATE(Reference to date formatted column in data source),LASTDATE(Reference to date formatted column in data source))

Then you should connect the date on the Table you created with the date on the Fact table.

From now on you'll use the date table for everything (calculations, fields on visuals, etc.).

There will probably be a new Date Hierarchy on the new Date Table, if there isn't, create it (right click...).

After this you'll have Year, Quarter, Month and Day fields and the sorting is always good.

[K_Hermann]

thank you for your answer. I tried to fill in the right dates but I can't seem to get it.

I have a colum in excel with Year and Month. which is 2018 and the months in text: june, july, august..

What do I need to fill in?

[K_Hermann]

[Anonymous]

Calender = CALENDAR(FIRSTDATE('FactTable'[Date]);LASTDATE(FactTable'[Date]))

Change the name of table and column. This way you have a dynamic Calendar Table always depending on Fact table, which is the recommended way.

[K_Hermann]

I only get errors. This is my data. So what do I need to fill in after FIRSTDATE(. Because I get errors if I fill in dates like 1, 8, 2018 and I get errors if I fill in Juni, 2018. So I don't get it. Do I need to make other tables in excel first?

[Anonymous]

Does it work for you?

[K_Hermann]

Yes! Thank you so much!

[Anonymous · Accepted solution]

Open the attached file. I created a dummy table "Sales", then created the Calendar table like I told you, then created the relationship and you'll see the date hierarchy on the Calendar Table.

https://www.dropbox.com/s/xhj8oz54j1agp6k/Sample.pbix?dl=0