Help with Dates and Date Formats and Proper Sorting

[irnm8dn]

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", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

Additionally, I have added a column and DAX statement that allows me to have a Week Starting:

Week Starting = DATEADD(
'Date'[Date],
1-WEEKDAY('Date'[Date])
,DAY
)

Most of bases are covered, however I am also looking to represent days of the week on a axis with the ability to choose what days of the week I include on the axis.  

For instance, I may want to represent Monday through Friday in chronological date order; or I may want to show performance on a specific day of week in chronological order.  i.e. YTD performance on Monday's.  I would imagine the format would be something like Wednesday - 5/31/2017.

Questions:

1. How do I add a column that will output a format like Wednesday - 5/31/2017?
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?
3. How do I sort order to make sure that the days I represent are in chronological order?

Thanks in advance.

[v-qiuyu-msft · Accepted solution]

Hi @irnm8dn,

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