Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Help with Dates and Date Formats and Proper Sorting

I currently use the following DAX statement to construct a date table:   Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year"...
Need Help
Show and Tell
Tips and Tricks
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    1. How do I add a column that will output a format like Wednesday - 5/31/2017?

     

    You can combine the date column and DayOfWeek column with DAX below:

    CONCATENATE('Date'[DayOfWeek],CONCATENATE(" - ",'Date'[Date]))

     

    Or you can drag date column and DayOfWeek column to the X-axis then click the button, see: August Power BI Desktop Update: Updated Drill experience.

     

     


    2.How do I select specific day(s) of the week to include on the axis?  1.i.e. Show me YTD performance for individual days but only include M,T,W,Th,Fr?

     

    You can add DayOfWeek column into Visual Level Filters pane, then select those weekdays, M,T,W,Th,Fr.

     

     

    3.How do I sort order to make sure that the days I represent are in chronological order?

     

    As you place both date and DayOfWeek column in X-axis, the axis value will be sorted based on date. Also you can do the custom sort trick via the suggestion from v-haibl-msft: Custom Sorting in PowerBI?.

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi Anonymous,

 

1. How do I add a column that will output a format like Wednesday - 5/31/2017?

 

You can combine the date column and DayOfWeek column with DAX below:

CONCATENATE('Date'[DayOfWeek],CONCATENATE(" - ",'Date'[Date]))

 

Or you can drag date column and DayOfWeek column to the X-axis then click the button, see: August Power BI Desktop Update: Updated Drill experience.

 

 


2.How do I select specific day(s) of the week to include on the axis?  1.i.e. Show me YTD performance for individual days but only include M,T,W,Th,Fr?

 

You can add DayOfWeek column into Visual Level Filters pane, then select those weekdays, M,T,W,Th,Fr.

 

 

3.How do I sort order to make sure that the days I represent are in chronological order?

 

As you place both date and DayOfWeek column in X-axis, the axis value will be sorted based on date. Also you can do the custom sort trick via the suggestion from v-haibl-msft: Custom Sorting in PowerBI?.

 

Best Regards,
Qiuyun Yu