Power BI User Group of Philadelphia

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Date Table with Holiday/Weekend Column

  • 1.  Date Table with Holiday/Weekend Column

    Top Contributor
    Posted Apr 18, 2019 05:01 PM
    ​​Step 1- Date Table
    Date =
    ADDCOLUMNS (
    CALENDAR (DATE(2016,1,1), DATE(2018,2,21)),
    "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" )

    Step 2 - Add some calculated columns in the following order
    1) IsWeekday =
    ( WEEKDAY('Date'[Date].[Date]) > 1
        && WEEKDAY( 'Date'[Date].[Date] ) < 7
    ) * 1

    2) Day Count of the Month = COUNTROWS(
                    FILTER('Date',
    'Date'[YearMonthShort]=EARLIER('Date'[YearMonthShort])
    && 'Date'[DateAsInteger] < EARLIER('Date'[DateAsInteger])
    && 'Date'[DayOfWeek] = EARLIER('Date'[DayOfWeek])

    ))+1

    3) Holiday = SWITCH
    (
    'Date'[MonthNameLong]
    ,"January",IF('Date'[DayNumber] = 1,"New Year's Day",IF('Date'[DayOfWeekShort]="Mon" && 'Date'[Day Count of the Month] = 3,"Martin Luther King, Jr",""))
    ,"February",IF('Date'[DayOfWeekShort]="Mon" && 'Date'[Day Count of the Month] = 3,"Washington's Birthday",IF('Date'[DayNumber]=14,"Valentine's Day",""))
    ,"May",IF('Date'[DayOfWeekShort]="Mon" && 'Date'[Day Count of the Month] = Max('Date'[Day Count of the Month]),"Memorial Day","")
    ,"July",IF('Date'[DayNumber]=4,"Independence Day","")
    ,"September",IF('Date'[DayOfWeekShort]="Mon" && 'Date'[Day Count of the Month] = 1,"Labor Day","")
    ,"October",IF('Date'[DayOfWeekShort]="Thu" && 'Date'[Day Count of the Month] = 2,"Columbus Day","")
    ,"November",IF('Date'[DayNumber]=11,"Veteran's Day",IF('Date'[DayOfWeekShort]="Thu" && 'Date'[Day Count of the Month] = 4,"Thanksgiving Day",""))
    ,"December",IF('Date'[DayNumber]=25,"Christmas Day","")
    )

    4) IsHoliday = IF('Date'[Holiday]<>"",1,0)


    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------


  • 2.  RE: Date Table with Holiday/Weekend Column

    Posted Jun 21, 2019 12:00 AM
    Hi Brian,
    I was working on a requirement recently where I had to create a Date Dimension table(with just the date) and link it to another table date field. When i used this Date dimension table's date field as a slicer, it did not work properly. I mean it did not filter the results correctly. If I widen the slicer it shows all the records. However when i filter it down to what I need I dont see the correct results. Any idea why it wont compare the two dates accurately?

    ------------------------------
    Sunil Raheja
    Aha Apps
    Glen Allen VA
    8045441123
    ------------------------------



  • 3.  RE: Date Table with Holiday/Weekend Column

    Gold Contributor
    Posted Jun 21, 2019 01:09 PM
    I had the same problem recently. I had the date formatted as a date in one table and datetime in the other. Once I changed the datetime field to date only, the slicer worked just fine.

    ------------------------------
    Christopher Schnaars
    LTI
    ------------------------------