Creating a Visual Calendar in Power BI

It has come time my attention (since I'm the one who tried it), that the setup instructions need to be modified if your date table is retrieved from an external source in a DirectQuery report (as opposed to a pure DAX date table made by CALENDAR or CALENDARAUTO).

Here are the changes:

Week of Month =
VAR __currWeek =
    WEEKNUM ( DimDate[Date], 1 )
VAR __startWeek =
    WEEKNUM ( DATE ( [Year], [Month Number], 1 ), 1 )
RETURN
    __currWeek - __startWeek + 1

Day Name = SWITCH(DimDate[Day of Week], 
   1, "Sunday", 
   2, "Monday", 
   3, "Tuesday", ...etc

Adjust the number/name pairs to fit your requirements.

This same feature could also be accomplished by creating a new table (using Enter Data), putting the 7 number/name pairs in there, and doing a 1-to-many join to DimDate on DimDate[Day of Week]. Then for the visual you would use the name from the new table (also, the step of sorting "Day Name" by "Day of Week" takes place on the new table instead of the calendar table).

Day Label =
IF (
    DimDate[Day Number] = 1,
    IF (
        DimDate[Month Number] = 1,
        "'" & YEAR ( DimDate[Date] ) - 2000 & " " & 
           DimDate[Month Abbreviation] & " " & DimDate[Day Number],
        DimDate[Month Abbreviation] & " " & DimDate[Day Number]
    ),
    "" & DimDate[Day Number]
)

This code returns "'21 Jan 1", "Feb 1" (and the day number for all dates not the first of the month). Alter to fit your requirements.

Please comment if you have any questions about my methods or if you come across any other deviations.