Time Intelligent Slicer to change X-Axis(Year/Month/Day)

[Anonymous]

Hi there @amitchandak @parry2k  @AlB and everyone else

I have created a dynmic slicer consistring of Day, Month and Year. When selecting the Month from slicer it automatically updates the x axis on my bar chart, similar for the day as well. The issue I'm having is that the months and days are out of order.

 

The way I acheived this I created a table with the three types (month, day, year) in one column, and then all the value types based on my data

 

The actual values are from a measure I calculated inside the above table. These two tables are what I use to create the bar graph. The bar graph as an axis of "Values" which are picked from the slicer "type", and the values of the bar graph comes from the "Measure SN DT"

Is there a way to sort the table or measure so that the months and days are displayed in order (jan,feb,march....) and (1,2,3,4,5,6,7)?

Thanks!

[Greg_Deckler · Accepted solution]

@Anonymous - It's a little difficult to tell how to get this extra sort by column in there since you are creating the table in DAX. Probably possible but can you maybe post some sample source data for the table you are cross joining, I think it is your CATARINA GL DATE table?

This might also help: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528

[Anonymous]

@Greg_Deckler  is correct your having this issue because your column is set to text.  you need ot create a proper calendar table has a column for each similar to the below 

[Anonymous]

The simplest way would be to add an index column. Then add the index column to the tooltips section under visualization, then click the three dots on the top right and go to sort, sort by index, sort ascending. 

[Anonymous]

@Anonymous 

Any guidance on cdoe the new index column? How would it know to categorize months in order and the days?

[Greg_Deckler · Accepted solution]

@Anonymous - It's a little difficult to tell how to get this extra sort by column in there since you are creating the table in DAX. Probably possible but can you maybe post some sample source data for the table you are cross joining, I think it is your CATARINA GL DATE table?

This might also help: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528

[Anonymous]

@Greg_Deckler @Anonymous here's a link to the raw data and stripped down version of the PBI. Really appreciate the help. Reading your article right now 

https://drive.google.com/drive/folders/1sS3T2ItgHs32jDAW708bfpYpWHzcEyRd?usp=sharing 

[Anonymous · Accepted solution]

that's one work around. 

https://1drv.ms/u/s!AnIEh6WhI4Jogq9y6FzYKR8pieOIJw?e=6jW4bR

[Anonymous]

Great solution! I"m sure this way should be faster as its loading in the powerquery instead of doing it as tables.
Thanks!

[Anonymous]

@Anonymous @Anonymous @Greg_Deckler  Thanks for the input guys, I got it to work! 
Created an Index Table as @Greg_Deckler  mentioned in the article attached

Then I made a the relationship between my table with all the measures for the slicer and the new index table connecting my "Values"

Finally took @Anonymous note and added the "Index" column as a tool tip in my graph and then sorted the graph by "Index"
Now the dates are in chronological order! THanks everyone!!

I'm sure there's probably a more effiecnt way of doing this...like creating an index column in the original slicer table without having to make another table. Will do some digging to clean it up but really thankful for everyones help!

[Greg_Deckler]

@Anonymous - Well, you probably need another numeric column that has values 1 - 12 for the months, 1 - 31 for the days, etc. Use Sort By functionality to sort your one field by this new field.

[Anonymous]

Thanks for reploy @Greg_Deckler , any guidance on what forumlas to use to create the new column?
Also if I load historic data then I will end up having more than just one year. Currently have 2020 data, but soon will have 2017-today. I'm assuming I'd have to give those numeric values 1-4 for the years correct?