Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Time Intelligent Slicer to Dynamically Change X-axis Year/MonthDay

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!

Need Help

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    First in your "CATARINA GT DATE"table create 2 calculated columns to get the Month both in whole number format and text format;

    Then in the new table,create 3 calculated column as below:

    Get month number = LOOKUPVALUE('Table'[Month no],'Table'[Month],'Table 2'[Value],blank())
    Convert to whole number = SWITCH('Table 2'[Type],
"year",'Table 2'[Value],"Month",'Table 2'[Get month number],"Day",'Table 2'[Value])
    X axis = 'Table 2'[Value]

    Then in the column "convert to whole number",remember to change the data type to "whole number";

    Column "x axis" is a copied column of "value",and it will used as X axis value.

    Finally select the column and choose sort by "convert to whole number":

    And you will see;

    For details,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!