Using DAX to Populate Pay Period

talk2gwhite · December 28, 2020, 3:49am

I have a list of employees and the date they started work. Each start date falls within a certain numbered pay period and fiscal year. There are 26 pay periods that are 14 days long and run from Sunday to Saturday.

I have a PDF calendar that lists the pay period number and the fiscal year. But I don’t have it in Excel. Is there a way to use DAX to populate this data into my date table? I used the date table code from Melissa. Ultimately I want to be able to visualize how many employees came on board per pay period and fiscal year.122320 StartDate Sample.xlsx (127.9 KB) 2020 PayPeriod Calendar.pdf (36.2 KB) PayPeriod and PayPeriodYear.pbix (227.6 KB)

BrianJ · December 28, 2020, 8:32am

@talk2gwhite,

There are a number of different ways you can do this, but here’s the approach I took:

I found a DOJ source for federal pay period start and end dates that was in better tabular format than the PDF file, and imported that through the Get Data web connection
merged that query (StartDate2) with the Dates table on Start Date
expanded resulting tables to include Pay Period, Start Date, End Date from StartDate2 in my Dates table
selected those three columns and used the Fill Down command to fill the remaining dates of the pay period
created a “FY & PP” custom column in Power Query using the following M code (line 76)
created a “PPinFY” custom column in Power Query using the following M code (line 77). Note that this is necessary in order to properly sort the FY in PP field created above, and have it show up in the proper order in the column chart below:

Now all that’s necessary is a simple COUNTROWS measure to count personnel:
Count Personnel = COUNTROWS( StartDates )

Drop the fields and measure into the column chart and voilà:

I hope this is helpful. Full solution file attached below.

Brian
eDNA Forum - PayPeriod and PayPeriodYear Solution.pbix (213.1 KB)

talk2gwhite · December 28, 2020, 4:42pm

Definitely gonna try this today! I’ll let you know how it goes.

talk2gwhite · December 28, 2020, 9:22pm

Couldn’t open the pbix file. I have the June 2020 version. Is there a way to save it so that it is in an earlier version?

BrianJ · December 28, 2020, 10:19pm

@talk2gwhite,

Unfortunately, Power BI doesn’t let you save back into earlier version formats.

I believe that the best way to handle this (other than you upgrading to the December 2020 version), would be in your current version of the file to paste this code below into a new query named StartDates2:

let
    Source = Pdf.Tables(Web.Contents("https://www.justice.gov/crt/file/840091/download"), [Implementation="1.2"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pay Period", Int64.Type}, {"Pay Period Begins (Sunday)", type date}, {"Pay Period Ends (Saturday)", type date}, {"Official Pay Date (Thursday)*", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Pay Period Begins (Sunday)", "PP Start Date"}, {"Pay Period Ends (Saturday)", "PP End Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Official Pay Date (Thursday)*"})
in
    #"Removed Columns"

and replace the M code in your Dates table with the following:

//let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
StartDate = #date(2014,1,1),
EndDate = #date(Date.Year(CurrentDate)+1, 12, 31),
FYStartMonthNum = 10,
Holidays = null,
WDStartNum = null,

//Start of the date table code

    FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
    //the WDStartNum parameter allows you to set Weekday numbering running from 0-6 or 1-7 but first day of the week will remain Monday
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
    InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),
    InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "MonthEnding", each Date.EndOfMonth([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertMonthEnding, "ISO Weeknumber", each
      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),
    BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOday = Table.AddColumn(InsertISOyear, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
    InsertCalendarWk = Table.AddColumn(InsertISOday, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
    InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),

    InsertMonthnYear = Table.AddColumn(InsertWeeknYear , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
    AddFY = Table.AddColumn(InsertQuarternYear, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
    AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFM = Table.AddColumn(AddFQ, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth then [MonthOfYear] - (FYStartMonth-1) else [MonthOfYear] + (12-FYStartMonth+1), type text),

    InsertIsAfterToday = Table.AddColumn(AddFM, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
    InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
    InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
    InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
    InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

    //InsertDayOffset = Table.AddColumn(InsertDayType, "DayOffset", each Number.From([Date] - CurrentDate), type number),  //if you enable DayOffset, don't forget to adjust the PreviousStepName in the next line of code.
    InsertWeekOffset = Table.AddColumn(InsertDayType, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
    InsertMonthOffset = Table.AddColumn(InsertWeekOffset, "MonthOffset", each ((12 * Date.Year([Date])) +  Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) +  Date.Month(Date.From(CurrentDate))), type number),
    InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) +  Date.QuarterOfYear(Date.From(CurrentDate))), type number),
    InsertYearOffset = Table.AddColumn(InsertQuarterOffset, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),

    IdentifyCurrentDate = Table.SelectRows(InsertYearOffset, each ([Date] = CurrentDate)),
    CurrentYear = IdentifyCurrentDate{0}[Year],
    CurrentMonth = IdentifyCurrentDate{0}[MonthOfYear],
    InsertFYoffset = Table.AddColumn(InsertYearOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - 
      (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
    RemoveToday = if EndDate < CurrentDate then Table.SelectRows(InsertFYoffset, each ([Date] <> CurrentDate)) else InsertFYoffset,
    InsertCompletedWeek = Table.AddColumn(RemoveToday, "WeekCompleted", each [WeekEnding] < Date.From(Date.EndOfWeek(CurrentDate)), type logical),
    InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "MonthCompleted", each [MonthEnding] < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
    InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
    InsertChangedType = Table.TransformColumnTypes(InsertCompletedQuarter,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(InsertChangedType, {"Date"}, StartDates2, {"PP Start Date"}, "StartDates2", JoinKind.LeftOuter),
    #"Expanded StartDates2" = Table.ExpandTableColumn(#"Merged Queries", "StartDates2", {"Pay Period", "PP Start Date", "PP End Date"}, {"Pay Period", "PP Start Date", "PP End Date"}),
    #"Filled Down" = Table.FillDown(#"Expanded StartDates2",{"Pay Period", "PP Start Date", "PP End Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Pay Period] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "FY & PP", each [Fiscal Year]&" PP"& Text.PadStart( Text.From( [Pay Period] ), 2, "0")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PPinFY", each "20"&Text.Middle( [Fiscal Year], 2, 2)& Text.PadStart( Text.From( [Pay Period] ), 2, "0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"PPinFY", Int64.Type}, {"FY & PP", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "PPinYr", each "20"&Text.Middle( Text.From( [Year] ), 2, 2)& Text.PadStart( Text.From( [Pay Period] ), 2, "0")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"PPinYr", Int64.Type}})
in
    #"Changed Type1"
//in
//fnDateTable

This won’t totally reproduce my file, but it will capture 95% of the work I did and the rest of it (creation of the measure and the column chart) should be easy for you to do once the power query transformations are complete.

– Brian

talk2gwhite · December 29, 2020, 2:32am

Did you load the DOJ table into Power Query first or did you do something else? I got this error when I added your code

BrianJ · December 29, 2020, 10:38am

@talk2gwhite,

I did some research and testing, and that also seems to be a result of using an out of date version. Try changing 1.2 to 1.1, and it should still work, but not throw the error.

– Brian

talk2gwhite · December 29, 2020, 8:38pm

My agency holds us back. That’s why I’m using June 2020. Are you using a personal copy or your agency machine? Are your IT folks more lenient? We can chat offline if you like.

BrianJ · December 29, 2020, 11:03pm

@talk2gwhite,

Fortunately, our office is really responsive on PBI updates - they usually push the new version within 48 hours of Microsoft releasing it.

I think if you just change the 1.2 in the line below to 1.1 you should be able to get the M code I provided above to run in the June 2020 version. If not, try omitting the entire second parameter of the Pdf.Tables function:

Source = Pdf.Tables(Web.Contents("https://www.justice.gov/crt/file/840091/download"), [Implementation="1.2"]),

Brian

talk2gwhite · January 4, 2021, 4:06am

Brian, thank you! I was able to recreate everything everything. There was only one challenge I came across. As long as I filter a single FY at a time, there was no issue. If I tried to tackle two years, the sorting of the FY and PP on the X-axis didn’t work. I was thinking that I might need an index column of some sort. But I don’t know how to do that. What do you think?

Heather · January 4, 2021, 4:42pm

Since you were unable to open @BrianJ’s original file to see how he solved this, I thought I’d step in with a screenshot for you

the index column needed goes on your calendar/date table (for Brian’s file, this was named “PPinFY”)
the code used for that column is included in the calendar M code in Brian’s post above:

#"Added Custom2" = Table.AddColumn(#"Changed Type", "PPinYr", each "20"&Text.Middle( Text.From( [Year] ), 2, 2)& Text.PadStart( Text.From( [Pay Period] ), 2, "0")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"PPinYr", Int64.Type}})

after loading your calendar/date table to the model, you need to sort your FY & PP column by that value (select the column to sort, then click Sort by Column, and choose the column to sort by):

talk2gwhite · January 4, 2021, 11:38pm

Thank you! I appreciate the support.